Sql Query Help!

[R.Chan]

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?

/r

[Mischa Sandberg]

Re: Sql Query Help!

SELECT Customers.name,
Purchases.datep urchase,
Purchases.item
FROM Customers
JOIN Purchases on Purchases.custI D = Customers.uid
WHERE Purchases.datep urchase =
(SELECT MAX(datepurchas e) FROM Purchases
WHERE Purchases.custI D = Customers.uid)

"R.Chan" <raychan_uoft@y ahoo.com> wrote in message
news:5460359e.0 407281601.6f58a bdc@posting.goo gle.com...[color=blue]
> 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?
>
> /r[/color]