Beg. SQL Programming Chapter 7 Exercise Quest 3
Here's the exercise problem from Chapter 7, exercise 3 (for the Northwind database):
Show the five most recent orders that were purchased from a customer who has spent more
than $25,000 with Northwind.
The T-SQL Answer provided in the book (shown below), doesn't show the order information, only the CustomerID. How can I show the Order Number as well? If I simply add "o.OrderID" to the subquery, I get the following error:
Msg 8120, Level 16, State 1, Line 1
Column 'Orders.OrderID' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Msg 116, Level 16, State 1, Line 1
Only one expression can be specified in the select list when the subquery is not introduced with EXISTS.
Here is the T-SQL answer as provided in the book:
SELECT TOP 5 *
FROM Orders oo
WHERE CustomerID IN
FROM Customers c
JOIN Orders o
ON c.CustomerID = o.CustomerID
JOIN [Order Details] od
ON o.OrderID = od.OrderID
GROUP BY c.CustomerID
HAVING SUM(UnitPrice * (1-Discount) * Quantity) > 25000
ORDER BY OrderDate