Advertisement
Advertisement


SQL selecting rows by most recent date


Question

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

Desired:

    CHARGEID    CHARGETYPE  SERVICEMONTH
1   101     R       8/1/2008
2   161     N       2/1/2008
2008/10/09
1
106
10/9/2008 9:05:02 PM

Accepted Answer

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
2008/10/09
147
10/9/2008 9:55:36 PM


SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth 
FROM invoice
GROUP BY chargeId, chargeType
2008/10/09

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
2016/08/18

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...

2016/06/30

Source: https://stackoverflow.com/questions/189213
Licensed under: CC-BY-SA with attribution
Not affiliated with: Stack Overflow
Email: [email protected]