sql server (alternate): Sql Query Help! -- sql server (alternate)

Sql Query Help!

raychan_uoft NO[at]SPAM yahoo.com
7/28/2004 5:01:22 PM

sql server (alternate): Hi,
I'm stuck on the following query. Any help would be greatly appreciated!

table1: Customers
uid, name
1, bob
2, jane
3, john

table2: Purchases
uid, custID, datepurchase, item
1, 1, 1/1/2004, mouse
2, 1, 1/2/2004, keyboard
3, 1, 1/3/2004, usb key
4, 2, 1/1/2004, mouse
5, 2, 6/19/2004, keyboard
6, 3, 1/1/2004, printer

I want a query to show what customers' last purchase is.
i.e. results:
bob, 1/3/2004, usbkeyboard
jane, 9/19/2004, keyboard
john, 1/1/2004, printer

Does anyone know how to do this?

Re: Sql Query Help!

Mischa Sandberg
7/29/2004 1:46:18 AM

SELECT Customers.name,
Purchases.datepurchase,
Purchases.item
FROM Customers
JOIN Purchases on Purchases.custID = Customers.uid
WHERE Purchases.datepurchase =
(SELECT MAX(datepurchase) FROM Purchases
WHERE Purchases.custID = Customers.uid)

[quoted text, click to view]