Questions

SQL Help?

+
0 Votes
Locked

SQL Help?

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:

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
  • +
    0 Votes
    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.

    +
    0 Votes
    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.

  • +
    0 Votes
    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.

    +
    0 Votes
    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.