Execute the following Microsoft SQL Server Transact-SQL (T-SQL) script in Management Studio (SSMS) Query Editor, SQLCMD or other client software 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: