Consider a problem
We have to write a query to return the top 3 orders for each customer in adventure works database.
We can do so by the following 3 ways
1-
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
CROSS APPLY
(SELECT TOP(3) *
FROM Sales.SalesOrderHeader
WHERE CustomerID = C.CustomerID
ORDER BY TotalDue DESC) AS O
ORDER BY
CustomerID ASC, TotalDue DESC
2-
SELECT CustomerID, SalesOrderID, TotalDueFROM ( SELECT O.CustomerID, O.SalesOrderID, O.TotalDue, ROW_NUMBER = ROW_NUMBER() OVER ( PARTITION BY O.CustomerID ORDER BY O.TotalDue DESC) FROM Sales.SalesOrderHeader AS O ) AS dWHERE d.ROW_NUMBER <= 3
We can change above query for better understanding as
with O AS
(
SELECT ROW_NUMBER()
OVER (PARTITION BY CustomerID ORDER BY TotalDue DESC)
AS rn ,Sales.SalesOrderHeader.* FROM Sales.SalesOrderHeader
)
SELECT C.CustomerID,
O.SalesOrderID,
O.TotalDue
FROM
AdventureWorks.Sales.Customer AS C
Inner Join O on C.CustomerID=O.CustomerID
Where O.rn<=3
No comments:
Post a Comment