Execute the following Microsoft SQL Server T-SQL example script in Management Studio Query Editor to demonstrate listing of the top 5 orders for each customer.

-- SQL find top n records - T-SQL row number over partition by

USE AdventureWorks;

GO

DECLARE @n tinyint

SET @n = 5

SELECT   Customer,

         SalesOrderID,

         -- MSSQL format currency (money)

         DollarAmount = '$'+LEFT( convert(varchar,DollarAmount,1),

                                len(convert(varchar,DollarAmount,1))-3)

FROM     (SELECT Customer = s.Name,

                 soh.SalesOrderID,

                 DollarAmount = soh.TotalDue,

                 SequenceNumber = ROW_NUMBER()

                 OVER(PARTITION BY soh.CustomerID ORDER BY soh.TotalDue DESC)

          FROM   Sales.SalesOrderHeader AS soh

                 INNER JOIN Sales.Store s

                   ON soh.CustomerID = s.CustomerID) t

WHERE    t.SequenceNumber <= @n

ORDER BY Customer

GO

-- (2686 row(s) affected)

/* Partial results

Customer                SalesOrderID      DollarAmount

A Bike Store            45283             $37,643

A Bike Store            46042             $34,722

A Bike Store            44501             $26,128

A Bike Store            43860             $14,603

A Great Bicycle Company 44125             $3,450

A Great Bicycle Company 44793             $2,828

A Great Bicycle Company 45569             $2,828

A Great Bicycle Company 46378             $2,224

A Great Bicycle Company 49537             $622

A Typical Bike Shop     46343             $39,156

A Typical Bike Shop     44755             $37,725

A Typical Bike Shop     45536             $28,429

A Typical Bike Shop     44092             $5,440

*/

----------

-- SQL find top n records - RANK windowing function over partition by

-- RANK function may result in more than n records in a partition due to equal matches

USE AdventureWorks;

GO

DECLARE @n tinyint

SET @n = 5

SELECT   Customer,

         SalesOrderID,

         -- MSSQL format currency (money)

         DollarAmount = '$'+LEFT( convert(varchar,DollarAmount,1),

                                len(convert(varchar,DollarAmount,1))-3)

FROM     (SELECT Customer = s.Name,

                 soh.SalesOrderID,

                 DollarAmount = soh.TotalDue,

                 SequenceNumber = RANK()

                 OVER(PARTITION BY soh.CustomerID ORDER BY soh.TotalDue DESC)

          FROM   Sales.SalesOrderHeader AS soh

                 INNER JOIN Sales.Store s

                   ON soh.CustomerID = s.CustomerID) t

WHERE    t.SequenceNumber <= @n

ORDER BY Customer

GO

-- (2690 row(s) affected)