plsql help please...??

[zannick]

hello oracle masters,
i really need help with this sql query. I have a table with the following sample data:
ID ITEM DATE QTY
1 disk1 01-01-02 10
2 disk2 01-01-02 11
3 disk3 01-01-02 11
4 disk1 02-01-02 10
5 disk2 02-01-02 10
6 disk1 03-01-02 9

problem: i need to get the most updated record for a specific item so for item disk 1, need to get the record id no 6 because its the latest for the item disk 1. i'm really having trouble getting the sql right. i tried the following but didn't work:
----------
select item, date, qty, max(id)
from tablename
where item='disk1'
group by id, item, date, qty
----------
this didn't work.
i need to show the id, item, date and qty in the report...

if anybody has a suggestion... i'd really appreciate it...
zannick-

 

[swampBoogie]

select * from tablename as t
where where "DATE" =
(select max("DATE&quot from tablename
where item = t.item)

 

[claude69]

If you want to know the date of the last update you must specificate the max date)

select item, max(date), qty,id
from tablename

Bye Claude

 

[lewisp]

If you want to do it in PL/SQL,such as in a report or form, you can order by the date DESC. You can then OPEN and FETCH just one record.