How to code for TOP 5 for each category 1

[kvince]

Greetings,

I have a table with three columns: Specialty, Diagnosis and Diag_count. I'm looking for the TOP 5 (by count) Diagnosis codes for each Specialty. I could use some help with the code. Since I'm on SQL Server 2000 I have no qualms about using TOP.

 

[PHV]

Perhaps this ?
SELECT A.Specialty, A.Diagnosis, A.Diag_count
FROM theTable A INNER JOIN theTable B ON A.Specialty = B.Specialty AND A.Diag_count <= B.Diag_count
GROUP BY A.Specialty, A.Diagnosis, A.Diag_count
HAVING COUNT(*) <= 5

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[kvince]

PHV

Thank you for your help! I'm still new at this. The code works, but I'm not sure why (please excuse my ignorance). I thought GROUP BY always involved an aggregation in the SELECT statement.

 

[r937]

GROUP BY does always involve aggregation (that is exactly what it is for)

however, there is no requirement to use an explicit aggregate function

notice that in PHV's query there is an aggregate function, but it's in the HAVING clause

r937.com | rudy.ca