SQL selecting rows by most recent date
Using the following query and results, I'm looking for the most recent entry where the ChargeId and ChargeType are unique.
select chargeId, chargeType, serviceMonth from invoice CHARGEID CHARGETYPE SERVICEMONTH 1 101 R 8/1/2008 2 161 N 2/1/2008 3 101 R 2/1/2008 4 101 R 3/1/2008 5 101 R 4/1/2008 6 101 R 5/1/2008 7 101 R 6/1/2008 8 101 R 7/1/2008
CHARGEID CHARGETYPE SERVICEMONTH 1 101 R 8/1/2008 2 161 N 2/1/2008
You can use a GROUP BY to group items by type and id. Then you can use the MAX() Aggregate function to get the most recent service month. The below returns a result set with ChargeId, ChargeType, and MostRecentServiceMonth
SELECT CHARGEID, CHARGETYPE, MAX(SERVICEMONTH) AS "MostRecentServiceMonth" FROM INVOICE GROUP BY CHARGEID, CHARGETYPE
So this isn't what the requester was asking for but it is the answer to "SQL selecting rows by most recent date".
SELECT t.chargeId, t.chargeType, t.serviceMonth FROM( SELECT chargeId,MAX(serviceMonth) AS serviceMonth FROM invoice GROUP BY chargeId) x JOIN invoice t ON x.chargeId =t.chargeId AND x.serviceMonth = t.serviceMonth
Read more… Read less…
SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth FROM invoice GROUP BY chargeId, chargeType
I see most of the developers use inline query without looking out it's impact on huge data.
in simple you can achieve this by:
select a.chargeId, a.chargeType, a.serviceMonth from invoice a left outer join invoice b on a.chargeId=b.chargeId and a.serviceMonth <b.serviceMonth where b.chargeId is null order by a.serviceMonth desc
select to.chargeid,t0.po,i.chargetype from invoice i inner join (select chargeid,max(servicemonth)po from invoice group by chargeid)t0 on i.chargeid=t0.chargeid
The above query will work if the distinct charge id has different chargetype combinations.Hope this simple query helps with little performance time into consideration...