Double group by with max date

Question

I've been having a look around am wondering how I can do a double group by. I have this fees table:

name	end_date	last_check	fee
bob	2019-01-01	2020-10-11	42
bob	2019-01-01	2020-11-25	41.45
bob	2020-01-01	2020-12-20	24.32
bob	2020-01-01	2021-02-03	29.30
sam	2019-01-01	2020-10-11	27.23
sam	2020-01-01	2020-12-20	23.23
sam	2020-01-01	2020-12-27	320.3
sam	2020-01-01	2021-02-03	320.3
bill	2021-01-01	2021-01-01	28.23

I want to first group by the name. Then group by the end_date but only select the most recent row based on the last_check. So my desired output would be:

name	end_date	last_check	fee
bob	2019-01-01	2020-11-25	41.45
bob	2020-01-01	2021-02-03	29.30
sam	2019-01-01	2020-10-11	27.23
sam	2020-01-01	2021-02-03	320.3
bill	2021-01-01	2021-01-01	28.23

My table:

CREATE TABLE fees (
 name varchar2(32) not null,
 end_date date not null,
 last_check date not null,
 fee number(5, 2) not null
);

INSERT INTO fees (name, end_date, last_check, fee) VALUES ('bob', TO_DATE('2019-01-01','YYYY-MM-DD'), TO_DATE('2020-10-11','YYYY-MM-DD'), 42);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('bob', TO_DATE('2019-01-01','YYYY-MM-DD'), TO_DATE('2020-11-25','YYYY-MM-DD'), 41.45);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('bob', TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2020-12-20','YYYY-MM-DD'), 24.32);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('bob', TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2021-02-03','YYYY-MM-DD'), 29.3);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('sam', TO_DATE('2019-01-01','YYYY-MM-DD'), TO_DATE('2020-10-11','YYYY-MM-DD'), 27.23);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('sam', TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2020-12-20','YYYY-MM-DD'), 23.23);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('sam', TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2020-12-27','YYYY-MM-DD'), 320.3);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('sam', TO_DATE('2020-01-01','YYYY-MM-DD'), TO_DATE('2021-02-03','YYYY-MM-DD'), 320.3);
INSERT INTO fees (name, end_date, last_check, fee) VALUES ('bill', TO_DATE('2021-01-01','YYYY-MM-DD'), TO_DATE('2021-01-01','YYYY-MM-DD'), 28.23);

My attempt:

SELECT name, end_date, max(last_check), fee
from fees
GROUP BY name, end_date, fee

Does this answer your question? [Fetch the row which has the Max value for a column](https://stackoverflow.com/questions/121387/fetch-the-row-which-has-the-max-value-for-a-column) — PM 77-1, Nov 01 '21 at 23:23
@PM77-1 I have already tried that and it did not work. That was actually the exact question I went to to try it out. — Zoey Malkov, Nov 01 '21 at 23:24
@ZoeyMalkov if you don't have ids for your tables, you will be resolve a lot of problems of your own -self stick on the bike- — Leandro Bardelli, Nov 01 '21 at 23:26
There are several solutions there that would've worked for you. — PM 77-1, Nov 01 '21 at 23:32

score 2 · Accepted Answer · edited Nov 01 '21 at 23:29

2

A way is using row_number()!

If you need build SQLs frequently, please, learn about "partition by".. its change your work!

select
   a.*
from
   (
      select
         f.*,
         row_number() over(partition by f.name, f.end_date order by f.last_check desc) as rn
      from
         fees f
   ) a where a.rn = 1;

edited Nov 01 '21 at 23:29

Leandro Bardelli

10,561
15
79
116

answered Nov 01 '21 at 23:28

Eric Alves da Rocha

51
3

1

Nice answer, +1. You understand what he needs (I think) – Leandro Bardelli Nov 01 '21 at 23:31
1

Seriously, your life will be change learning about row_number(), dense_rank(), partition by with ordery BY (try SUM() with partition by... its magic hehehe) https://www.postgresql.org/docs/9.1/tutorial-window.html – Eric Alves da Rocha Nov 01 '21 at 23:31

score 0 · Answer 2 · answered Nov 02 '21 at 20:26

Another one to keep in your arsenal. It might not be as performant, but I find it more descriptive of the intent

select distinct 
       name, 
       end_date, 
       max(last_check) over (partition by name, end_date), 
       first_value(fee) over (partition by name, end_date order by last_check desc)
from fees;

Double group by with max date

2 Answers2