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.