MySQL: Get distinct values of column row order by date

I’m trying to get a set of results that are sorted by descending date and where the value of a column (zone_id) is not repeated, but I can’t find the right query.

The column I am really interested in is the 2nd column (zone_id) but I have shown the id column in order to make it clearer the result I want to obtain.

The table simply sorted by date has the following data:

id	zone_id	updated_at
206	63	2021-05-11 09:22:19
205	63	2021-05-11 09:21:53
204	22	2021-05-11 09:21:38
5	33	2021-05-10 10:45:09
6	23	2021-05-10 10:45:09
3	3	2021-05-10 10:45:02
137	63	2021-03-06 17:02:25
143	67	2021-03-03 20:24:14
98	14	2020-11-06 14:49:44
177	29	2020-08-16 20:09:26

So that, in this case the values it should receive should be:

63, 22, 33, 23, 3, 67, 14, 29

I have tried the following queries without success:

SELECT zone_id, id, updated_at 
FROM table 
GROUP BY zone_id 
ORDER BY updated_at DESC

shows: 23, 33, 3, 67, 84, 63, 88, 98,…

SELECT DISTINCT zone_id, id, updated_at 
FROM table 
ORDER BY updated_at DESC

shows: 63, 63, 22, 33, 23, 3, 63, 67,…

Please, can someone tell me what I am doing wrong?

Thank you very much in advance:

Answer

I think you want row_number() to choose one row per zone and then ordering:

select t.*
from (select t.*,
             row_number() over (partition by zone_id order by updated_at desc) as seqnum
      from t
     ) t
where seqnum = 1
order by updated_at desc;

Advertisement

Answer