SQL row selection by last date

Question

Using the following query and results, I am looking for the most recent entry where the ChargeId and ChargeType properties 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 

Answer 1

You can use GROUP BY to group items by type and ID. You can then use the MAX () Aggregate function to get the last month of service. Below is the result with the parameters ChargeId, ChargeType and MostRecentServiceMonth

 SELECT CHARGEID, CHARGETYPE, MAX(SERVICEMONTH) AS "MostRecentServiceMonth" FROM INVOICE GROUP BY CHARGEID, CHARGETYPE 

Answer 2

So this is not what the requestor requested, but it is the answer to "SQL row selection by last date".

Changed from http://wiki.lessthandot.com/index.php/Returning_The_Maximum_Value_For_A_Row

 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 

Answer 3

 SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth FROM invoice GROUP BY chargeId, chargeType 

Answer 4

I see that most developers use an inline query without looking at its impact on huge data.

in simple form you can achieve this:

 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 

Answer 5

 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 request will work if a separate charge identifier has different combinations of a charging type. We hope that this simple query will help with short execution time ...