Selecting the maximum date before a certain year

I have a table where I’d like to select only the record with the maximum date:

ID    Date
01    05MAY2005
01    23JUN2020
01    23JAN2022

The issue is that I have some IDs in the table where the maximum date is in 2022, so that is what gets selected. How do I select the maximum date for a record that is before 01JAN2022 so that my output look like this:

01    23JUN2020

Answer

There are multiple ways to achieve this. The simplest is probably to sort your records by descending date and fetch only the first one. If you don’t want rows with a certain condition, such as dates after 01JAN2022, just add a WHERE.

You didn’t indicate a specific RDBMS so your exact syntax may vary a bit.

select *
from YourTable
where date < '2022-01-01'
order by date desc
fetch first 1 row only