[SQL]How to return rows of a list of items that has their individual max date?

22 Views Asked by At

Currently I have a list of strings, let's say ('A,'B','C','D','E'), and say there is a table that has multiple dates associated with these strings.

IE table looks something like this.

String     Dates
A         Jan 1
A         Feb 1 
A         Mar 1
B         Dec 1
B         Oct 1
B         Jan 1
C         Jan 1
C         Oct 1
C         Nov 1
D         Jan 1
D         Feb 1
D         Apr 1
E         Apr 1
E         Mar 1
E         Oct 1

And I want to just have rows that have the max date, but of each String only one time. IE:

String     Dates
A         Mar 1
B         Dec 1
C         Nov 1
D         Apr 1
E         Oct 1

Currently, I have a bunch of unions. It works, but I feel like there should be a faster way to do what Im doing.

select * from table where String = 'A' and Dates = (select max(Dates) from table where String = 'A' )
union all
select * from table where String = 'B' and Dates = (select max(Dates) from table where String = 'B' )
union all
select * from table where String = 'C' and Dates = (select max(Dates) from table where String = 'C' )
union all
select * from table where String = 'D' and Dates = (select max(Dates) from table where String = 'D' )
union all
select * from table where String = 'E' and Dates = (select max(Dates) from table where String = 'E' )

1

There are 1 best solutions below

1
AudioBubble On

We can use max and group by as below. As there are no values in the table which are not in the list we could leave out the line where id in ('A','B','C','D','E')

select
  id, 
  max(day)
from StringDates
where id in  ('A','B','C','D','E')
group by id
order by id;
id | max(day)  
:- | :---------
A  | 2022-03-01
B  | 2022-12-01
C  | 2022-11-01
D  | 2022-04-01
E  | 2022-10-01

db<>fiddle here