Select most recent records 1

[spiff2002]

We have a table where we keep track of inventory received from our suppliers. Obviously the table can have multiple entries for the same Item but their dates and Qty received are different.

The table should look like this:

Table receipt
item_id Qty Date_recived
1 20 11/11/03
3 40 11/10/03
6 30 11/10/03
1 45 11/08/03
3 40 11/07/03
: : :
: : :
: : :
47 30 01/03/03

I need to run a Query that selects the most recent entrie for each item within a date range
For instance the result on the previous table with dates 01/01/03 to 11/30/03 should be:
1 20 11/11/03
3 40 11/10/03
6 30 11/10/03
47 30 01/03/03

Thanks a lot for any clue that anyone can give me on this.

 

[swampBoogie]

select * from receipt r
where date_received = (
select max(date_received)
  from receipt
 where r.item_id = item_id)

 

[nicsin]

try

select *
from receipt
group by item_ID, Qty
having Date_recived = max(Date_recived)

 

[JonFer]

nicsin - your SQL is a little off there. Try running that yourself and see what problems Spiff would have had.

swampBoogie has the correct answer although the SQL is missing the request for the date range criteria.

 

[nicsin]

thnx JonFer. I will do but I haven't got access on that machine... Sorry spiff!

 

[spiff2002]

Thanks all
Like Jonfer said, swampBoogie had the right answer, I already added the Date range and it worked beautifully.
Thanks anyway nicsin. I may be able to use your query ina different way.