/* Query 6. The Orders table in [MyGuitarShop] database contains orders of many customers. If we know the CustomerID of a specific customer, say, 432, we can easily find this customer's orders by a WHERE clause with a condition 'CustomerID = 432'. When 432's orders are found, we can ask any query, for example, her newest order by using MAX(OrderDate) in the SELECT clause. The statement would look like SELECT OrderID, MAX(OrderDate) FROM Orders WHERE CustomerID = 432;

The challenge is, what if we want EVERY customers newest order, not just a specific customer's? And the output contains EmailAddress, OrderID, and OrderDate of the newest order. If every customer has only one order, then this becomes a trivial query because the output is nothing but contains every customer's only order. Unfortunately, this is not the case in most business.

To solve this challenging query, firstly you need a join to get all three output columns. Secondly, you may be thinking to put MAX(OrderDate) into the SELECT clause as shown above. Unfortunately, if you do so, the MAX() applies to all rows of the join result (review p.162~3 of Chapter 5 to know why), meaning you only get one row returned, that is whichever customer's order that has the newest date compared to every order of every customer. In other words, you won't get the order with the newest date of EVERY customer.

Because the join result contains every customer's orders, to solve the above challenging query, you are supposed to compare the order date of orders that belong to the same customer, not orders of everybody. This should be a clear enough hint to you, meaning if you add a WHERE clause to filter rows of the join result, you can pass the row's CustomerID to a subquery, which can use the CustomerID to search Orders table to find his/her newest order. The subquery will very similar to the SELECT statement shown above except 432 is replaced with the CustomerID passed from the main query. Because the WHERE will examine one row a time, if the row's order date does not match the newest date returned by the subquery, that row is discarded and will not be passes to the SELECT clause for output.

If you don't understand this process, please read the above two paragraphs.

Once the main query and subquery are drafted down, you only need to tie them up by matching with order date. If you make all correctly, you are using a correlated subquery to return one row per customer, representing the customers newest order.

Each row in the output should include these three columns: EmailAddress, OrderID, and OrderDate. Sort the results by the EmailAddress column in ascending sequence. Hint: 35 rows, including the newest order of david.goldstein@hotmail.com, who has three orders and the newest one should be order 29. */

+ o - External Tables dbo.Addresses o Columns AddressID (PK, int, not null) CCustomerID (FK, int, null) Line1 (varchar(60), not null) Line2 (varchar(60), null) City (varchar(40), not null) State (varchar(2), not null) Zip Code (varchar(10), not null) Phone (varchar(12), not null) Disabled (int, not null) Keys + Constraints # Triggers # Indexes Statistics dbo.Administrators Columns AdminlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(255), not null) FirstName (varchar(255), not null) LastName (varchar(255), not null) # Keys + Constraints # Triggers Indexes Statistics dbo.Categories o Columns CategoryID (PK, int, not null) CategoryName (varchar(255), not null) # Keys + Constraints # Triggers Indexes Statistics dbo.Customers Columns CustomerlD (PK, int, not null) EmailAddress (varchar(255), not null) Password (varchar(60), not null) FirstName (varchar(60), not null) LastName (varchar(60), not null) ShippingAddressID (int, null) BillingAddressID (int, null) # # - dbo.Orderltems Columns ItemID (PK, int, not null) - OrderID (FK, int, null) - ProductID (FK, int, null) ItemPrice (money, not null) DiscountAmount (money, not null) Quantity (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Orders Columns OrderID (PK, int, not null) 6 CustomerlD (FK, int, null) OrderDate (datetime, not null) ShipAmount ( money, not null) TaxAmount (money, not null) ShipDate (datetime, null) ShipAddressID (int, not null) CardType (varchar(50), not null) CardNumber(char(16), not null) CardExpires (char(7), not null) BillingAddressID (int, not null) Keys Constraints Triggers Indexes Statistics dbo.Products Columns To ProductID (PK, int, not null) - CategoryID (FK, int, null) Product Code (varchar(10), not null) ProductName (varchar(255), not null) Description (text, not null) ListPrice (money, not null) DiscountPercent (money, not null) DateAdded (datetime, null) Keys Constraints Triggers Indexes Statistics # #