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

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



Solution 1:[1]

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

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1