SQL-find the ratings with the max complete_date with group by-CodePudding

Please run the DDL and DML on sqlfiddle. I am trying to come up with a query to find the ratings of the FID that has the max complete_date

DDL:

create table crr (rating varchar(255), FID int, complete_date varchar(255));

DML:

insert into crr values ('low', 182, '2021/11/1'),('medium', 182,'2022/6/14'), ('medium', 369,'2021/9/1')
, ('medium', 369,'2021/11/9'), ('medium', 429,'2021/4/5'), ('medium', 429,'2021/4/6'),('high', 429,'2022/12/5');

my query is:

Select c.fid, rating, c.complete_date 
From crr c
Inner Join (
Select fid, Max(complete_date)as d 
From crr
Group By fid)A;
On c.fid=A.fid and c.complete_date=d;

It is not returning what I wanted. Ideal output would be:

rating FID complete_date
medium 182 2022/6/14
medium 369 2021/11/9
high 429 2022/12/5

It should be easy-peasy, please advise.

CodePudding user response：

Is that what you are looking for?

select * from crr a
where complete_date = (select max(complete_date) from crr b where b.fid=a.fid)
order by fid

I changed completed_date to date, with varchar I was having different results

create  table crr (rating varchar(255), FID int, complete_date date);

insert into crr values ('low', 182, '2021/11/1'),('medium', 182,'2022/6/14'), ('medium', 369,'2021/9/1')
, ('medium', 369,'2021/11/9'), ('medium', 429,'2021/4/5'), ('medium', 429,'2021/4/6'),('high', 429,'2022/12/5');

CodePudding user response：

Select rating, c.fid, c.complete_date 
From crr c
Inner Join (
Select fid, Max(complete_date) as d
From crr
Group By fid) as A On c.fid=A.fid and c.complete_date=A.d;

Date is a string, then sorting will be in lexicographic order