| How to
find the 5 largest orders for each customer? |
|
Execute the following
script in Query Editor to list the top 5 orders by dollar amount
for each customer:
USE AdventureWorks
GO
SELECT Customer, SalesOrderID, DollarAmount
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 < 6
ORDER BY Customer
GO
|
| The World Leader
in SQL Server Training |
|