SQL selecting rows by most recent date with two unique columns

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

score 160 · Accepted Answer · edited Oct 09 '08 at 21:55

160

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

edited Oct 09 '08 at 21:55

Adam Pierce

33,531
22
69
89

answered Oct 09 '08 at 21:07

Mitchel Sellers

62,228
14
110
173

1

Don't forget to alias the MAX(serviceMonth) field in case you need it downstream. – Ben Hoffstein Oct 09 '08 at 21:15
2

ok, so what happens if there is a row 101 N 1/1/2008 in the table? – tvanfosson Oct 09 '08 at 21:16
3

you would get an extra row returned. Which is the desired result based on his requirements. – Carlton Jenke Oct 09 '08 at 21:18
1

Added alias to the query in post. tvanfosson - that would be a conditional that added to the result set, which is correct. – Mitchel Sellers Oct 09 '08 at 21:19
Sorry. I need to read more carefully. I was thinking row that matched max date for a given charge id. – tvanfosson Oct 09 '08 at 21:23
You might want to change the title. It's misleading. – tvanfosson Oct 09 '08 at 21:27
2

If I wanted to also pull the record id with it. How would I do that? – Donny V. Aug 27 '10 at 19:39

score 64 · Answer 2 · edited Oct 13 '11 at 14:45

64

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

edited Oct 13 '11 at 14:45

Community

1
1

answered Oct 09 '08 at 21:22

tvanfosson

524,688
99
697
795

Doesn't seem to be compatible with views. – marknuzz Feb 24 '16 at 23:19

score 11 · Answer 3 · answered Oct 09 '08 at 21:09

11

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

answered Oct 09 '08 at 21:09

Ben Hoffstein

102,129
8
104
120

score 11 · Answer 4 · answered Aug 18 '16 at 11:10

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

score 1 · Answer 5 · edited Jun 30 '16 at 12:25

1

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

edited Jun 30 '16 at 12:25

Michael Benjamin

346,931
104
581
701

answered Jun 30 '16 at 12:15

pari elanchezhiyan

21
1

score 0 · Answer 6 · edited Jul 05 '22 at 07:18

Demo at sqlfiddle:

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
);

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.

SQL selecting rows by most recent date with two unique columns

6 Answers6

Linked