How to select distinct based on condition (another column) [duplicate]

Question

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?

Answer 1

You will need aggregation via group by and the max date, filtering out rows that are older to 1) ensure that your grouping occurs faster and 2) ignore items that have no newer date values.

SELECT house, people, max(`date`)
FROM Table
WHERE `date` > '2021-10-20 00:00:00'
GROUP BY house, people