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

Answer 1

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

Answer 2

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

Answer 3

So this isn't what the requester was asking for but it is the answer to "SQL selecting rows by most recent date".

Modified 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 id) x 
    JOIN invoice t ON x.chargeId =t.chargeId
    AND x.serviceMonth = t.serviceMonth