I am looking for a solution to the problem from Beginning Microsoft SQL Server® 2008 Programming book.(AdventureWorks database used)
Ques> Show the most recent five orders that were purchased from account numbers that have spent more than $70,000 with AdventureWorks.
I found a solution to this on See query but this is not working. What's the error in this query?
SELECT ss.AccountNumbeR, OrderDate
FROM Sales.SalesOrderHeader ss
WHERE ss.TotalDue > 70000
AND ss.AccountNumber NOT IN (SELECT TOP 5 ss.AccountNumber
FROM Sales.SalesOrderDetail so
JOIN Sales.SalesOrderHeader ss
ON SO.SalesOrderID = ss.SalesOrderID
HAVING SUM(so.LineTotal > 70000))
GROUP BY ss.TotalDue, ss.AccountNumber, OrderDate
ORDER BY OrderDate DESC
I'm not entirely sure why you though this query would work:
It's querying for orders where the account number is not in the top 5 which is the opposite of what we want, moreover TOP 5 here has no ordering so it's entirely random. And this is entirely in the wrong place, the question wants the top 5 orders, not the top 5 accounts.
It's also filtering for orders where each of the orders individually has a value greater than 70000. And HAVING SUM(so.LineTotal > 70000) is a syntax error.
I would advise you instead to use window functions here
SELECT s.AccountNumber, s.OrderDate
FROM (
SELECT so.*,
TotalPerAccount = SUM(ss.LineTotal) OVER (PARTITION BY so.AccountNumber),
rn = ROW_NUMBER() OVER (PARTITION BY so.AccountNumber ORDER BY so.OrderDate DESC)
FROM Sales.SalesOrderHeader so
JOIN Sales.SalesOrderDetail ss ON so.SalesOrderID = ss.SalesOrderID
) s
WHERE s.TotalPerAccount > 70000
AND s.rn <= 5;