Double group by with max date-CodePudding

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

CodePudding user response：

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;