November 26, 2006 at 5:46 am
Hi, i've these following table
licenses_renew
TransID | VehicleID | RenewDate
----------------------------------------
1 | AU2985 | 10/18/05
2 | BX3290 | 9/29/06
3 | AU2985 | 10/19/06
4 | BX3290 | 9/19/06
5 | BW4474 | 7/11/06
I need to query, 'how to display only the latest RenewDate of each VehicleID?'. So, the result is
TransID | VehicleID | RenewDate
----------------------------------------
2 | BX3290 | 9/29/06
3 | AU2985 | 10/19/06
5 | BW4474 | 07/11/06
????
November 26, 2006 at 9:56 am
select vehicleid, max( renewdate)
from licenses_renew
group by vehicleid
If you need trans id, wrap this in a select like
select transid, vehid, renew
from (select vehicleid, max( renewdate) as renew
from licenses_renew
group by vehicleid
) a
inner join licenses l
on a.vehid = l.vehid and a.renew = l.renew_date
Viewing 2 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply