SYSK 140: Ranking Functions in SQL 2005
Do you know the difference between ROW_NUMBER and RANK functions? What about NTILE? If not, read on…
ROW_NUMBER
Returns the sequential number of a row within a partition of a result set, starting at 1 for the first row in each partition. The ORDER BY clause determines the sequence in which the rows are assigned their unique ROW_NUMBER within a specified partition.
For example, the query below produces the following results:
USE AdventureWorks
GO
SELECT c.FirstName, c.LastName, ROW_NUMBER() OVER(ORDER BY SalesYTD DESC) AS 'Row Number', s.SalesYTD, a.PostalCode
FROM Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0
=>
FirstName LastName Row Number SalesYTD PostalCode
-------------------------------------------------------------------------------------------
Shelley Dyck 1 5200475.2313 98027
Gail Erickson 2 5015682.3752 98055
Maciej Dusza 3 4557045.0459 98027
Linda Ecoffey 4 3857163.6332 98027
Mark Erickson 5 3827950.238 98055
Terry Eminhizer 6 3587378.4257 98055
Michael Emanuel 7 3189356.2465 98055
Jauna Elson 8 3018725.4858 98055
Carol Elliott 9 2811012.7151 98027
Janeth Esteves 10 2241204.0424 98055
Martha Espinoza 11 1931620.1835 98055
Carla Eldridge 12 1764938.9859 98027
Twanna Evans 13 1758385.926 98055
The RANK function is similar to ROW_NUMBER. The key difference is if rows with tied values exist, they will receive the same rank value. For example, if the two top salespeople have the same SalesYTD value, they are both ranked one.
DENSE_RANK is another ranking function in SQL 2005. This function is almost identical to RANK. The only difference is DENSE_RANK doesn't return gaps in the rank values.
USE AdventureWorks;
GO
SELECT i.ProductID, p.Name, i.LocationID, i.Quantity, DENSE_RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as DENSE_RANK, RANK() OVER (PARTITION BY i.LocationID order by i.Quantity) as RANK
FROM Production.ProductInventory i JOIN Production.Product p ON i.ProductID = p.ProductID
ORDER BY i.ProductID;
GO
=>
ProductID Name LocationID Quantity DENSE_RANK RANK
1 Adjustable Race 1 408 57 78
1 Adjustable Race 6 324 52 71
1 Adjustable Race 50 353 82 122
2 Bearing Ball 6 318 50 67
2 Bearing Ball 1 427 62 85
3 BB Ball Bearing 1 585 82 110
Finally, NTILE function divides the result set into a specified number of groups, based on the ordering and optional partition. For each row, NTILE returns the number of the group to which the row belongs.
USE AdventureWorks;
GO
SELECT c.FirstName, c.LastName, NTILE(4) OVER(ORDER BY SalesYTD DESC) AS 'Quartile', s.SalesYTD
From Sales.SalesPerson s JOIN Person.Contact c on s.SalesPersonID = c.ContactID
JOIN Person.Address a ON a.AddressID = c.ContactID
WHERE TerritoryID IS NOT NULL AND SalesYTD <> 0;
GO
=>
FirstName LastName Quartile SalesYTD
-----------------------------------------------------------
Shelley Dyck 1 5200475.2313
Gail Erickson 1 5015682.3752
Maciej Dusza 1 4557045.0459
Linda Ecoffey 1 3857163.6332
Mark Erickson 2 3827950.238
Terry Eminhizer 2 3587378.4257
Michael Emanuel 2 3189356.2465
Jauna Elson 3 3018725.4858
Carol Elliott 3 2811012.7151
Janeth Esteves 3 2241204.0424
Martha Espinoza 4 1931620.1835
Carla Eldridge 4 1764938.9859
Twanna Evans 4 1758385.926
Source: SQL Server 2005 Books Online.