2

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?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Paul Viorel
  • 234
  • 1
  • 11

2 Answers2

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

Zakaria
  • 4,715
  • 2
  • 5
  • 31
  • @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
0

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
Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175