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