Web Development



SQL Help?

By donallsop ·
I'm stumped on what seems to be a simple SQL query.

My data source is a table with fields Item, Date, and Quantity, like so:

Apples 11/12/08 10
Apples 11/13/08 42
Pears 11/11/08 13
Bananas 11/12/08 21
Apples 11/10/08 8
Bananas 11/14/08 15
Pears 11/10/08 16

I want to return the quantity found in the record with the latest date for each item.
So the output would be

Apples 11/13/08 42
Bananas 11/14/08 15
Pears 11/11/08 13

I can't figure this. I'm sure it will be obvious once somebody else tells me the answer.

This conversation is currently closed to new comments.

Thread display: Collapse - | Expand +

All Answers

Collapse -

nest SELECT with max

by john.a.wills In reply to SQL Help?

FROM TheTable x
WHERE x.date = (SELECT max (y.date)
FROM TheTable y
Where y.item = x.item)

I think that should do it.

Collapse -

Thank you

by donallsop In reply to nest SELECT with max

It seems like that should work. Unfortunately, the example I posted was very much simplified. The actual data source is a query that has about 13 more queries behind it, drawing from many tables. I tried your method and after about five minutes I gave up waiting for it to comlete. So I'm going to say that what I was told to do is possible, but not practical. Thanks for the help. I learned something about SQL and that's always good.

Related Discussions

Related Forums