Another interesting question one of my students has sent me recently. Given the following Orders table. As you can see, this table has four columns : customer number, supplier number, order number, and order date.
Create a query to display the highest order date for each customer. For example, using the values shown above, the query will retrieve the following result:
CREATE TABLE cust_orders (custid int, supid int, orderid int, orderdate datetime) INSERT INTO cust_orders VALUES (1,1,2,getdate() + 1) INSERT INTO cust_orders VALUES (1,2,3,getdate() + 2) INSERT INTO cust_orders VALUES (1,3,4,getdate() + 3) INSERT INTO cust_orders VALUES (1,4,5,getdate() + 4) INSERT INTO cust_orders VALUES (1,5,6,getdate() + 5) INSERT INTO cust_orders VALUES (1,6,7,getdate() + 6) INSERT INTO cust_orders VALUES (2,1,2,getdate() + 1) INSERT INTO cust_orders VALUES (2,2,3,getdate() + 2) INSERT INTO cust_orders VALUES (2,3,4,getdate() + 3) INSERT INTO cust_orders VALUES (2,4,5,getdate() + 4) INSERT INTO cust_orders VALUES (2,5,6,getdate() + 5) INSERT INTO cust_orders VALUES (2,6,7,getdate() + 6)umbers VALUES (29)
SELECT TabA.custid , TabA.supid , TabA.orderid, TabA.orderdate FROM cust_orders TabA JOIN (SELECT MAX(orderdate) AS orderdate , custid FROM cust_orders GROUP BY custid) as tabB ON TabA.custid = tabB.custid AND TabA.orderdate = tabB.orderDate