Answer to Question #182600 in Databases | SQL | Oracle | MS Access for Aditya Surana

Question

Show the most recent five orders that were purchased from account numbers that have spent more than $70,000 with AdventureWorks.

Answer 1

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