Simple solution now to a problem from 8 years ago. Use SQL windowing function
- by Kevin Shyr
Originally posted on: http://geekswithblogs.net/LifeLongTechie/archive/2014/06/10/simple-solution-now-to-a-problem-from-8-years-ago.aspxI 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!
