How to select distinct based on condition (another column)

January 24, 2022

I am trying to select distinct values from a table based on date column. I mean I want to extract the distinct rows with higher value of date column

ID| house | people| date
------------------------------
1 |   a   |   5   | 2021-10-20
2 |   a   |   5   | 2022-01-20
3 |   b   |   4   | 2021-10-20
4 |   b   |   4   | 2022-01-20

After query is runned, I want the below result:

   a   |   5   | 2022-01-20
   b   |   4   | 2022-01-20

I have tried below query but I have no idea how to add the condition (show the distinct row with higher date value.
SELECT DISTINCT house, people FROM Table

I tried SELECT DISTINCT house, people FROM Table WHERE MAX(date) but got some errors.

Any ideas?

>Solution :

You can get the row number for each row partitoned by house and ordered by date desc. then only select the rows with row number = 1:

select house, people, date
from(select house, people, date, row_number() over(partition by house order by date desc) rn
from table_name) t
where rn = 1

Fiddle