CODEWITHSUNDEEP
sqlamazon-redshift
user3609179
user3609179

Reputation: 301

Redshift list 3 most recent values per year

I have a column of dates and I want to find the three maximum dates for each year I have tried the following. 

select max(date, rank() over (partition by SPLIT_PART(date, '-', 1) order by date desc)
from table
;

My desired output would be 

2013,2010-12-31
2013,2010-12-30
2013,2010-12-29

also there are repeats dates in the table so I would have to filter those out as well

Upvotes: 0

Views: 561

Answers (1)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

Assuming there are no duplicate dates, you can partition by the year part of date and get the latest 3 dates per year. Use distinct (if needed) in the final query to remove the duplicates, if any.

select yr,date
from (select date_part(year,date) as yr,date
      ,dense_rank() over (partition by date_part(year,date) order by date desc) as rnk
     from table
     ) t
where rnk<=3

Upvotes: 1

Related Questions