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' )
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')db<>fiddle here