SQL selecting rows by most recent date with two unique columns

jgreep_16345 0
Viewed 374872

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
oracle sql
6 Answers

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
Mitchel_Sellers_132790

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 chargeId) x 
    JOIN invoice t ON x.chargeId =t.chargeId
    AND x.serviceMonth = t.serviceMonth
user_129500 
SELECT chargeId, chargeType, MAX(serviceMonth) AS serviceMonth 
FROM invoice
GROUP BY chargeId, chargeType
Ben_Hoffstein_44820

Demo at sqlfiddle:

  1. Classical way.
select 
    chargeid, 
    chargetype,
    SERVICEMONTH
from invoice t0
where t0.SERVICEMONTH = (
    select max(SERVICEMONTH) 
    from invoice t1 
    where t1.chargeid = t0.chargeid
    and t1.chargetype = t0.chargetype
);
  1. Using window functions.
with w_o as (
    select
        chargeid, 
        chargetype,
        SERVICEMONTH,
        row_number() OVER (PARTITION BY chargeid, chargetype ORDER BY SERVICEMONTH DESC) rn
    from invoice
)
select
    chargeid, 
    chargetype,
    SERVICEMONTH
from w_o
where rn = 1;

It is nice to understand and be able to use both styles.

user_48791790
Related