End date with null value :db2-sql-CodePudding

i have a table my_date

id        Start          END
1       2021-01-13     2021-04-15
1       2021-04-16     2021-11-28
1       2021-11-29        null
2       2021-05-05     2021-09-13
2       2021-09-13     2021-12-31
3       2020-01-09     2021-08-29
3       2021-08-30     2023-04-15

what i want to want to choose the id that have max(end)<= 2021-12-31 so my result should be just

id        Start          END
2       2021-09-13     2021-12-31

CodePudding user response：

SELECT id, start, end = MAX(end)
FROM my_date
GROUP BY id, start
HAVING MAX(end) <= '2021-12-31'

CodePudding user response：

You can filter by the date limit, then order by date in descending manner, and finally retrieve the first row only.

For example:

select *
from t
where end <= date '2021-12-31'
order by end desc
fetch next 1 rows only

Result:

ID  START       END
--  ----------  ----------
 2  2021-09-13  2021-12-31

See running example at db<>fiddle.