Simple solution now to a problem from 8 years ago. Use SQL windowing function

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!


[UPDATE 2014-08-28]: As KarKey pointed out, you'll have to put this into a derived table to use RowNum in the outer where clause. The query syntax as it is was run against a Teradata database successfully.

Print | posted on Tuesday, June 10, 2014 4:18 PM

Feedback

# re: Simple solution now to a problem from 8 years ago. Use SQL windowing function

Left by KarKey at 8/28/2014 3:59 AM
Gravatar Can you really use RowNum in your where condition while its being selected?

# re: Simple solution now to a problem from 8 years ago. Use SQL windowing function

Left by LifeLongTechie at 8/28/2014 10:26 AM
Gravatar I was able to run that against a Teradata database without any issue. I see your point though; just tried to run the same script in MSSQL 2008R2 and it complained. :(

Your comment:





 

Copyright © Kevin Shyr

Design by Bartosz Brzezinski

Design by Phil Haack Based On A Design By Bartosz Brzezinski