SQL Query to find the last event in a particular month-CodePudding

I am trying to query the ID's whose last entry lies within January month (01/01/2020 to 31/01/2020).

Data is as below

ID  DATE
 
123 25/01/2020
123 27/01/2020
123 30/01/2020
123 02/02/2020
456 17/01/2020
456 18/01/2020
456 19/01/2020
456 22/01/2020
789 30/01/2020
789 01/01/2020
654 03/01/2020
654 08/01/2020
654 10/01/2020
654 25/01/2020

Expected Output

ID  DATE

456 22/01/2020
654 25/01/2020

Thank you

CodePudding user response：

You can use group by and having:

select id, max(date)
from t
group by id
having max(date) >= date '2020-01-01' and
       max(date) < date '2020-02-01'