How to select distinct based on condition (another column)

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?

you can use group by, very similar to this https://stackoverflow.com/questions/3491329/group-by-with-maxdate — Tomáš Šturm, Jan 24 '22 at 15:25
I will give it a try, it looks similar to my problem. Thanks. I will be back with a response. — Paul Viorel, Jan 24 '22 at 15:29

score 1 · Accepted Answer · answered Jan 24 '22 at 15:27

1

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

answered Jan 24 '22 at 15:27

Zakaria

4,643
2
4
29

1

That worked very smoothly. Thank you so much. – Paul Viorel Jan 24 '22 at 15:56
@PaulViorel what if a (house, people) record does not have a new date? This solution does not seem to cope with that scenario. Or can we exclude that possibility? – Lajos Arpad Jan 24 '22 at 16:17
@LajosArpad I need that record, it keeps the date when that row is inserted into DataBase. – Paul Viorel Jan 26 '22 at 12:53
@PaulViorel okay, thanks for clarifying! – Lajos Arpad Jan 26 '22 at 13:20

score 0 · Answer 2 · answered Jan 24 '22 at 15:26

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

How to select distinct based on condition (another column)

2 Answers2