SQL Help. I need to select the lastest date from a col.

[CartoonDog]

Many thanks in advance.

Assume the table is as listed below.

Trans_ID Date_Entered Part_ID
-------- ------------ -------
1 01-DEC-01 1234
2 03-NOV-03 1234
3 01-SEP-02 1234
4 01-OCT-03 1111

I need to be able to select the row with the lastest date for a given part number. Such as:

Select Trans_ID, Date_Endtered, Part_ID
from table
where Part_ID='1234';

But how do I get only the row with the lastest date? I dont know if I am missing a basic command or need to do some sort of recursive query.

Thanks.

 

[avjoshi]

Select Trans_ID, Date_Endtered, Part_ID
from table
where (Trans_ID, Date_Endtered, Part_ID) in
(select Trans_ID, max(Date_Endtered), Part_ID
from table
where Part_ID='1234'
group by Trans_ID, Part_ID);

Anand.

 

[CartoonDog]

Thanks for the prompt response. But I am unable to make it work. I seem to get the same result (all rows with part number) as before. Here is the actual SQL statement.

set pagesize 50000
set linesize 2000

select transaction_id, part_id, workorder_base_id, qty, transaction_date
from inventory_trans IT
where IT.workorder_base_id is not null
and IT.part_id='250092'
and (IT.Transaction_ID, IT.transaction_date) in
(select Transaction_ID, max(transaction_date)
from inventory_trans IT2
where IT2.Part_ID='250092'
and IT2.workorder_base_id is not null
group by IT2.Transaction_ID, IT2.transaction_date);

 

[aryeh1010]

Try this:

select *
from trans
where (part_id, date_entered) in
(
select part_id, max(date_entered) date_entered
from trans
group by part_id
)

 

[CartoonDog]

Got It!

Im not sure why, but when multiple columns are selected with the in statement as you suggested, I get all rows back. When I pared it down to just the date, it works. Thanks for sending me down the right path.

Working code:

set pagesize 50000
set linesize 2000

select transaction_id, transaction_date
from inventory_trans IT
where IT.workorder_base_id is not null
and IT.part_id='250092'
and (IT.transaction_date) in
(select max(transaction_date)
from inventory_trans IT2
where IT2.Part_ID='250092'
and IT2.workorder_base_id is not null);

 

[avjoshi]

Thats because you are grouping by Transaction date in your second select statement. remove that and you will get correct results.

Anand.

 

[aryeh1010]

You could also try this,

select a.trans_id, a.part_id, a.date_entered
from trans a,
(select part_id, max(date_entered) date_entered
from trans
group by part_id) b
where a.part_id = b.part_id and
a.date_entered = b.date_entered