SQL Query help needed.......?

Question

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

Answer 1

SELECT

Item, MAX(Date), SUM(Quantity)

FROM myTable

GROUP BY Item

Answer 2

Unless I'm misreading something, whoever thumbed-dowm Jim's answer obviously hasn't a clue. His responce should work just fine for what the OP needs.

Answer 3

select t1.* from YourTable t1 join

(select item, max(date) md from YourTable group by item) t2

on t1.item=t2.item and t1.date=t2.md

Answer 4

This is one way to do it:



select item,date,qty from items as item1

where ddate = (select max(date) from items as item2 where item1.item = item2.item)