Originally posted on: http://geekswithblogs.net/LifeLongTechie/archive/2014/06/10/simple-solution-now-to-a-problem-from-8-years-ago.aspx

I remember having this problem 8 years ago. We had to find the top 5 donor per month and send out some awards. The SQL we came up with was clunky and had lots of limitation (can only do one year at a time), then switch the where clause and go again.

Fast forward 8 years, I got a similar problem where we had to find the top 3 combination of 2 fields for every single day. And the solution is this elegant:

SELECT
CAST(eff_dt AS DATE) AS "RecordDate"
, status_cd
, nbr
, COUNT(*) AS occurance
, ROW_NUMBER() OVER (PARTITION BY CAST(eff_dt AS DATE) ORDER BY COUNT(*) DESC) RowNum
FROM table1
WHERE RowNum < 4
GROUP BY
CAST(eff_dt AS DATE)
, status_cd
, nbr

If only I had this 8 years ago. :) Life is good now!