jgreep jgreep - 5 months ago 13
SQL Question

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


Desired:

CHARGEID CHARGETYPE SERVICEMONTH
1 101 R 8/1/2008
2 161 N 2/1/2008

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