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