Web Development

Ask something
Question Locked

SQL Help?

0 helpful
3 replies

by donallsop

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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:

ITEM DATE QTY
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.
TIA

3 replies

by john.a.wills

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

I think that should do it.

by donallsop

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.

This discussion is closed to new replies.