Query the latest info

Hi All,

I have thsi list of record

Chasis Status Date
pl1 sold 10/20/2004
pl1 return 10/21/2004
pl2 sold 10/24/2004
pl2 return 10/25/2004
pl3 sold 11/01/2004
pl4 sold 11/03/2004
pl4 return 11/04/2004
pl4 sold 11/06/2004

sp i want to list out cars that status solid has been sold

so in this case only pl3 and pl4 can be display. So anyone can advise me on this. thanks

Regards,
ShaffiqYou want to list only the cars that have been sold?

Select * from table where status = 'sold'|||You want to list only the cars that have been sold?

Select * from table where status = 'sold'

hi,
its easy, but sometime cars that has been sold were return back , so this not considered sold. I want to display cars that solid has been sold...sometimes got status sold, return and sold back... but sometimes only sold..for this case only Pl3 and PL4.. any idea? thanks|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')|||There's probably a more efficient way with joins but this may work:

select * from table where chasis not in (select chasis from table where status = 'return')

Thx,
I' d tried before but it will not display cars that have tree status( sold, return, sold) for this case you can see at PL4|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'|||i think this might do it --select chasis
from yourtable as X
where [Date] =
( select max([Date])
from yourtable
where chasis
= X.chasis )
and Status = 'sold'

Hi r937,

Thx for the solution it works well thank also to pshisbey