sql - PostgreSQL Select the r.* by MIN() with group-by on two columns

Question

The example schema of a table called results

id	user_id	activity_id	activity_type_id	start_date_local	elapsed_time
1	100	11111	1	2014-01-07 04:34:38	4444
2	100	22222	1	2015-04-14 06:44:42	5555
3	100	33333	1	2015-04-14 06:44:42	7777
4	100	44444	2	2014-01-07 04:34:38	12345
5	200	55555	1	2015-12-22 16:32:56	5023

question from:https://stackoverflow.com/questions/65869454/postgresql-select-the-r-by-min-with-group-by-on-two-columns

Answer 1

I think you want this:

SELECT DISTINCT ON (user_id, activity_type_id, EXTRACT(year FROM start_date_local)) 
     *, EXTRACT(year FROM start_date_local) AS year
FROM results
ORDER BY user_id, activity_type_id, year, elapsed_time;