CODEWITHSUNDEEP
sqlsql-server
Viking
Viking

Reputation: 465

sql, select top 5 occurences for certain dates

I have a table with diagnosecodes on my sql server. On several days some diagnoses are set and pushed in the table.

Table (diagnoses) looks like this: Diagnose, Diagnosecode, Date.

What im trying to output with sql is: The top 5 most occurred diagnosecode's on certain days (from X days till now) like this:

10 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

9 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

8 sept 2015 : 
100 times 1157 (diagnosecode)
90 times 11337 (diagnosecode)
80 times 111237 (diagnosecode)
70 times 112312357 (diagnosecode)
60 times 1112317 (diagnosecode)

I tried this:

SELECT TOP(5) count([diagnosecode]) as occurences, diagnosecode,     
FROM diagnoses 
WHERE date = SYSDATETIME()
GROUP BY diagnosecode 
ORDER BY date DESC, COUNT([diagnosecode]) DESC

It only works for today. I dont know how to make the query work for multiple days. How would I approach this issue?

Upvotes: 2

Views: 959

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270573

If you want five on each day, then use window functions:

SELECT date, occurrences, diagnosecode,     
FROM (SELECT diagnosecode, date, COUNT(*) as occurrences,
             ROW_NUMBER() OVER (PARTITION BY date ORDER BY COUNT(*) DESC) as seqnum
     FROM diagnoses 
     GROUP BY diagnoses, date
    ) dd
WHERE seqnum <= 5
ORDER BY date DESC, occurrences DESC;

Note: if the date really has a time component, then you will want to cast it to date to extract only the date portion.

Upvotes: 6

Chandrasekar Kesavan
Chandrasekar Kesavan

Reputation: 795

Remove the where clause

SELECT TOP(5) count([diagnosecode]) as occurences, diagnosecode,     
FROM diagnoses 
GROUP BY diagnosecode 
ORDER BY date DESC, COUNT([diagnosecode]) DESC

Upvotes: 0

Related Questions