SQL top + count() confusion
- by vasin
I've got the following table:
patients
id
name
diagnosis_id
What I need to do is get all the patients with N most popular diagnosis.
And I'm getting nothing using this query:
SELECT name FROM patients
WHERE diagnosis_id IN
(SELECT TOP(5) COUNT(diagnosis_id) FROM patients
GROUP BY diagnosis_id
ORDER BY diagnosis_id)
How to fix it?