Execute the following script in Query Editor to demonstrate the use of ROW_NUMBER function with PARTITION BY. The TOP 10 selects the largest orders for each customer.
USE AdventureWorks
GO
WITH cteTotalDueSorted
AS
(
SELECT
Customer = s.Name,
s.CustomerID,
SalesOrderID,
OrderDate,
TotalDue='$'+convert(varchar,TotalDue,1),
SeqNo = ROW_NUMBER() OVER (
PARTITION BY soh.CustomerID
ORDER BY TotalDue DESC)
FROM Sales.SalesOrderHeader AS soh
JOIN Sales.Store s
ON soh.CustomerID = s.CustomerID
)
SELECT
Customer,
ItemNo=cte.SeqNo,
SalesOrderID,
OrderDate = convert(char(10), OrderDate,111),
TotalDue
FROM cteTotalDueSorted cte
WHERE cte.SeqNo <= 10
ORDER BY CustomerID, ItemNo
Partial Results:
| Customer | ItemNo | SalesOrderID | OrderDate | TotalDue |
| A Bike Store | 1 | 45283 | 2002/2/1 | $31,972.17 |
| A Bike Store | 2 | 46042 | 2002/5/1 | $29,418.53 |
| A Bike Store | 3 | 44501 | 2001/11/1 | $22,152.24 |
| A Bike Store | 4 | 43860 | 2001/8/1 | $12,381.08 |
| Progressive Sports | 1 | 46976 | 2002/8/1 | $8,727.11 |
| Progressive Sports | 2 | 47997 | 2002/11/1 | $4,682.69 |
