SQL Server 2005 ranking functions – ROW_NUMBER()

ROW_NUMBER() is a new function that is added to the SQL Server 2005 T-SQL syntax. ROW_NUMBER() is used to assign ranks to the result of a query. The basic syntax is 

ROW_NUMBER()  OVER ([<partition_by_clause>]<order_by_clause>)

Here comes the first sample query that will return the top 10 selling items in the AdventureWorks OLTP database. A rank will be assigned based on the total sales “SUM(A.LineTotal)” of each item.
The “TOP 10” clause is used to obtain the top 10 rows based on “ORDER BY SUM(A.LineTotal) DESC” after the GROUP BY clause.
You should note that adding a clause like “WHERE RowNumber <= 10” or “WHERE ROW_NUMBER() <= 10” does *not* work here, as the ROW_NUMBER() function is done after the result set is obtained from the query.

SELECT TOP 10 RowNumber = ROW_NUMBER() OVER (ORDER BY SUM(A.LineTotal) DESC)
  , A.ProductID
  , ProductTotal = SUM(A.LineTotal)
  , B.ProductNumber
  , ProductName = B.Name
 FROM Sales.SalesOrderDetail AS A
  INNER JOIN Production.Product AS B
   ON A.ProductID = B.ProductID
 GROUP BY A.ProductID, B.ProductNumber, B.Name
 ORDER BY SUM(A.LineTotal) DESC

———- following is the result ———-

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            ProductID   ProductTotal
——————– ———– —————————————
1                    782         4400592.800400
2                    783         4009494.761841
3                    779         3693678.025272
4                    780         3438478.860423
5                    781         3434256.941928
6                    784         3309673.216908
7                    793         2516857.314918
8                    794         2347655.953454
9                    795         2012447.775000
10                   753         1847818.628000

==========
The following query returns the top 3 sales persons for each product.
“PARTITION BY” is used to reset the sequential number (the rank) to 1 for the first row in each partition (per ProductID in this case). A derived table is used here, as ROW_NUMBER() only affects the resulting rows.

SELECT Z.*
 FROM (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name) Z
 WHERE Z.RowNumber <= 3
 ORDER BY Z.ProductID, Z.RowNumber

—– CTE (common table expression) can also be used for the query above —–

WITH Z
AS (SELECT RowNumber = ROW_NUMBER() OVER (PARTITION BY A.ProductID ORDER BY SUM(A.LineTotal) DESC)
    , C.SalesPersonID
    , A.ProductID
    , ProductTotal = SUM(A.LineTotal)
    , B.ProductNumber
    , ProductName = B.Name
   FROM Sales.SalesOrderDetail AS A
    INNER JOIN Production.Product AS B
     ON A.ProductID = B.ProductID
    INNER JOIN Sales.SalesOrderHeader C
     ON A.SalesOrderID = C.SalesOrderID
   WHERE C.SalesPersonID IS NOT NULL
   GROUP BY C.SalesPersonID, A.ProductID, B.ProductNumber, B.Name)
SELECT Z.*
 FROM Z
 WHERE Z.RowNumber <= 3
 ORDER BY Z.ProductID, Z.RowNumber

———- following is part of the result ———-

(ProductNumber and ProductName are intentionally removed for better presentation)

RowNumber            SalesPersonID ProductID   ProductTotal
——————– ————- ———– —————————————
1                    276           707         10859.642388
2                    277           707         10410.012222
3                    285           707         9890.467534
1                    276           708         11677.535352
2                    277           708         11673.985659
3                    285           708         10129.459479
1                    279           709         1141.795500
2                    276           709         875.211250
3                    277           709         826.785950

 

— (result is cut) —

===== For more information =====

– ROW_NUMBER { http://msdn2.microsoft.com/en-us/library/ms186734.aspx }

– OVER clause { http://msdn2.microsoft.com/en-us/library/ms189461.aspx }

– ORDER BY clause { http://msdn2.microsoft.com/en-us/library/ms188385.aspx }

– Ranking functions { http://msdn2.microsoft.com/en-us/library/ms189798.aspx }

– What’s New in SQL Server 2005 { http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx }

– AdventureWorks – SQL Server 2005 Samples and Sample Databases (July 2006) { http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en }

– WITH common table expression { http://msdn2.microsoft.com/en-us/library/ms175972.aspx }

 

===== Other posts that point here =====

~ Pagination in MSSQL 2005

http://blog.pengoworks.com/index.cfm/2008/6/10/pagination-your-data-in-mssql-2005