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.