Summary of usage of the SQL Server sort functions ROW_NUMBER and RANK

1. Basic usage of ROW_NUMBER:

SELECT
SalesOrderID,
CustomerID,
ROW_NUMBER () OVER (order by SalesOrderID) AS RowNumber
FROM Sales. SalesOrderHeader
Result set:
SalesOrderID CustomerID RowNumber
-------------------------------------------
43659 676 1
43660 117 2
43661 442 3
43662 227 4
43663 510 5
43664 397 6
43665 146 7
43666 511 8
43667 646
:

2. Basic usage of RANK:

SELECT
SalesOrderID,
CustomerID,
RANK () OVER (order by CustomerID) AS Rank
FROM Sales. SalesOrderHeader
Result set:
SalesOrderID CustomerID Rank
--------------------------------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2 5
47997 2 5
49054 2 5
50216 2 5
51728 2 5
57044 2 5
63198 2 5
69488 2 5
44124 3 13
:

3. Use CTE to filter ROW_NUMBER () usage:

WITH NumberedRows
(
SELECT
SalesOrderID,
CustomerID,
ROW_NUMBER () OVER (order by SalesOrderID) AS RowNumber
FROM Sales. SalesOrderHeader
)

SELECT * FROM NumberedRows
WHERE RowNumber BETWEEN 100 AND 200
Result set:

SalesOrderID CustomerID RowNumber
------------------------------------------
43759 13257 100
43760 16352 101
43761 16493 102
:
43857 533 199
43858 36 200

4. Use ROW_NUMBER () with Group:

WITH CustomerSum
AS
(
SELECT CustomerID, SUM (TotalDue) AS TotalAmt
FROM Sales. SalesOrderHeader
Group by CustomerID
)
SELECT
*,
ROW_NUMBER () OVER (order by TotalAmt DESC) AS RowNumber
FROM CustomerSum
Result set:
CustomerID TotalAmt RowNumber
-------------------------------------------
678 1179857.4657 1
697 1179475.8399 2
170 1134747.4413 3
328 1084439.0265 4
514 1074154.3035 5
155 1045197.0498 6
72 1005539.7181 7
:

5. ROW_NUMBER () or RANK () Aggregation usage:

WITH CustomerSum
(
SELECT CustomerID, SUM (TotalDue) AS TotalAmt
FROM Sales. SalesOrderHeader
Group by CustomerID
)
SELECT *,
RANK () OVER (order by TotalAmt DESC) AS Rank
-- Or ROW_NUMBER () OVER (order by TotalAmt DESC) AS Row_Number
FROM CustomerSum
Result set of RANK:
CustomerID TotalAmt Rank
----------------------------------------------------
678 1179857.4657 1
697 1179475.8399 2
170 1134747.4413 3
328 1084439.0265 4
514 1074154.3035 5
:

6. Basic usage of DENSE_RANK:

SELECT
SalesOrderID,
CustomerID,
DENSE_RANK () OVER (order by mermerid) AS DenseRank
FROM Sales. SalesOrderHeader
WHERE CustomerID> 100
Result set:
SalesOrderID CustomerID DenseRank
-------------------------------------------
46950 101 1
47979 101 1
49048 101 1
50200 101 1
51700 101 1
57022 101 1
63138 101 1
69400 101 1
43855 102 2
44498 102 2
45280 102 2
46038 102 2
46951 102 2
47978 102 2
49103 102 2
50199 102 2
51733 103 3
57058 103 3
:

7. Comparison between RANK () and DENSE_RANK:

WITH CustomerSum
(
SELECT
CustomerID,
ROUND (CONVERT (int, SUM (TotalDue)/100, 8) * 100 AS TotalAmt
FROM Sales. SalesOrderHeader
Group by CustomerID
)
SELECT *,
RANK () OVER (order by TotalAmt DESC) AS Rank,
DENSE_RANK () OVER (order by TotalAmt DESC) AS DenseRank
FROM CustomerSum
Result set:
CustomerID TotalAmt Rank DenseRank
-------------------------------------------------
697 1272500 1 1
678 1179800 2 2
170 1134700 3 3
328 1084400 4 4
:
87 213300 170 170
667 210600 171 171
196 207700 172 172
451 206100 173 173
672 206100 173 173
27 205200 175 174
687 205200 175 174
163 204000 177 175
102 203900 178 176
:

8. Basic usage of NTILE:

SELECT
SalesOrderID,
CustomerID,
NTILE (10000) OVER (order by mermerid) AS NTile
FROM Sales. SalesOrderHeader
Result set:
SalesOrderID CustomerID NTile
-------------------------------------------
43860 1 1
44501 1 1
45283 1 1
46042 1 1
46976 2
47997 2
49054 2
50216 2
51728 2 3
57044 2 3
63198 2 3
69488 2 3
44124 3 4
:
45024 29475 9998
45199 29476 9998
60449 29477 9998
60955 29478 9999
49617 29479 9999
62341 29480 9999
45427 29481 10000
49746 29482 10000
49665 29483 10000

9. Comparison of all sorting methods:

SELECT
SalesOrderID AS OrderID,
CustomerID,
ROW_NUMBER () OVER (order by CustomerID) AS RowNumber,
RANK () OVER (order by CustomerID) AS Rank,
DENSE_RANK () OVER (order by CustomerID) AS DenseRank,
NTILE (10000) OVER (order by mermerid) AS NTile
FROM Sales. SalesOrderHeader
Result set:
OrderID CustomerID RowNumber Rank DenseRank NTile
------------------------------------------------------
43860 1 1 1 1
44501 1 2 1 1
45283 1 3 1 1 1
46042 1 4 1 1
46976 2 5 5 2 2
47997 2 6 5 2 2
49054 2 7 5 2 2
50216 2 8 5 2 2
51728 2 9 5 2 3
57044 2 10 5 2 3
63198 2 11 5 2 3
69488 2 12 5 2 3
44124 3 13 13 3 4
44791 3 14 13 3 4
:

10. Basic usage of partition:

SELECT
SalesOrderID,
SalesPersonID,
OrderDate,
ROW_NUMBER () OVER (partition by SalesPersonID order by OrderDate) AS OrderRank
FROM Sales. SalesOrderHeader
WHERE SalesPersonID IS NOT NULL
Result set:
SalesOrderID SalesPersonID OrderDate OrderRank
---------------------------------------------------------
:
43659 279 00:00:00. 000 1
43660 279 00:00:00. 000 2
43681 279 00:00:00. 000 3
43684 279 00:00:00. 000 4
43685 279 00:00:00. 000 5
43694 279 00:00:00. 000 6
43695 279 00:00:00. 000 7
43696 279 00:00:00. 000 8
43845 279 00:00:00. 000 9
43861 279 00:00:00. 000 10
:
48079 287 00:00:00. 000 1
48064 287 00:00:00. 000 2
48057 287 00:00:00. 000 3
47998 287 00:00:00. 000 4
48001 287 00:00:00. 000 5
48014 287 00:00:00. 000 6
47982 287 00:00:00. 000 7
47992 287 00:00:00. 000 8
48390 287 00:00:00. 000 9
48308 287 00:00:00. 000 10
:

11. partition by aggregate usage:
WITH CTETerritory
(
SELECT
Cr. Name AS CountryName,
CustomerID,
SUM (TotalDue) AS TotalAmt
FROM
Sales. SalesOrderHeader AS soh
Inner join Sales. SalesTerritory AS ter ON soh. TerritoryID = ter. TerritoryID
Inner join Person. CountryRegion AS cr ON cr. CountryRegionCode = ter.
CountryRegionCode
GROUP
Cr. Name, CustomerID
)
SELECT
*,
RANK () OVER (partition by CountryName order by TotalAmt, CustomerID DESC) AS Rank
FROM CTETerritory

Result set:

CountryName CustomerID TotalAmt Rank
----------------------------------------------------
Australia 29083 4.409 1
Australia 29061 4.409 2
Australia 29290 5.514 3
Australia 29287 5.514 4
Australia 28924 5.514 5
:
Canada 29267 5.514 1
Canada 29230 5.514 2
Canada 28248 5.514 3
Canada 27628 5.514 4
Canada 27414 5.514 5
:
France 24538 4.409 1
France 24535 4.409 2
France 23623 4.409 3
France 23611 4.409 4
France 20961 4.409 5
:

12. How to Use partition by to calculate the average:

WITH CTETerritory
(
SELECT
Cr. Name AS CountryName,
CustomerID,
SUM (TotalDue) AS TotalAmt
FROM
Sales. SalesOrderHeader AS soh
Inner join Sales. SalesTerritory AS ter ON soh. TerritoryID = ter. TerritoryID
Inner join Person. CountryRegion AS cr ON cr. CountryRegionCode = ter.
CountryRegionCode
GROUP
Cr. Name, CustomerID
)
SELECT
*,
RANK () OVER (partition by CountryName order by TotalAmt, CustomerID DESC) AS Rank,
AVG (TotalAmt) OVER (partition by CountryName) AS Average
FROM CTETerritory

Result set:

CountryName CustomerID TotalAmt Rank Average
---------------------------------------------------------------
Australia 29083 4.409 1 3364.8318
Australia 29061 4.409 2 3364.8318
Australia 29290 5.514 3 3364.8318
:
Canada 29267 5.514 1 12824.756
Canada 29230 5.514 2 12824.756
Canada 28248 5.514 3 12824.756