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. 


 


 

Comments (0)