I have the table as follows: And I'd like to select the max date for every year per user, so the result would be like: Some help? Thank you! Answer Just use aggregation: If you have more columns that you want, then use distinct on:

Select max date per year

I have the table as follows:

user_id  date
1        2020-11-15
1        2020-10-15
1        2020-09-15
1        2019-12-15
1        2019-11-15
2        2020-11-15
2        2020-10-15
2        2019-12-15
3        2020-10-15
3        2020-09-15

​x
 
user_id  date1        2020-11-151        2020-10-151        2020-09-151        2019-12-151        2019-11-152        2020-11-152        2020-10-152        2019-12-153        2020-10-153        2020-09-15​

And I’d like to select the max date for every year per user, so the result would be like:

user_id  date
1        2020-11-15
1        2019-12-15
2        2020-11-15
2        2019-12-15
3        2020-10-15

 
user_id  date1        2020-11-151        2019-12-152        2020-11-152        2019-12-153        2020-10-15​

Some help? Thank you!

Answer

Just use aggregation:

select user_id, max(date)
from t
group by user_id, date_trunc('year', date);

 
select user_id, max(date)from tgroup by user_id, date_trunc('year', date);​

If you have more columns that you want, then use distinct on:

select distinct on (user_id, date_trunc('year', date)) t.*
from t
order by user_id, date_trunc('year', date), date desc;

 
select distinct on (user_id, date_trunc('year', date)) t.*from torder by user_id, date_trunc('year', date), date desc;​

Advertisement

Answer