Tuesday 13 March 2012

SELECT ROW_NUMBER()

ROW_NUMBER() returns a column as an expression that contains the row’s number within the result set. This is only a number used in the context of the result set; if the result changes, the ROW_NUMBER() will change.

I have not written about this subject for long time, as I strongly believe that Book On Line explains this concept very well. SQL Server 2005 has total of 4 ranking function. Ranking functions return a ranking value for each row in a partition. All the ranking functions are non-deterministic.
ROW_NUMBER () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition.
RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of each row within the partition of a result set.
DENSE_RANK () OVER ([<partition_by_clause>] <order_by_clause>)
Returns the rank of rows within the partition of a result set, without any gaps in the ranking.
NTILE (integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)
Distributes the rows in an ordered partition into a specified number of groups.
All the above definition and syntax are taken from BOL. It is difficult to explain above function anything else than what they are explained in BOL. Following example is excellent example from BOL again. This function explains usage of all the four function together in one query.
USE AdventureWorks;GOSELECT c.FirstNamec.LastName,ROW_NUMBER() OVER (ORDER BY a.PostalCodeAS 'Row Number',RANK() OVER (ORDER BY a.PostalCodeAS 'Rank',DENSE_RANK() OVER (ORDER BY a.PostalCodeAS 'Dense Rank',NTILE(4OVER (ORDER BY a.PostalCodeAS 'Quartile',s.SalesYTDa.PostalCodeFROM Sales.SalesPerson sINNER JOIN Person.Contact cON s.SalesPersonID c.ContactIDINNER JOIN Person.Address aON a.AddressID c.ContactIDWHERE TerritoryID IS NOT NULL
AND 
SalesYTD <> 0;

Resultset:

No comments:

Post a Comment