SQL top + count() confusion

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?

SELECT name FROM patients
WHERE diagnosis_id IN
(
SELECT TOP(5) diagnosis_id FROM patients
GROUP BY diagnosis_id
ORDER BY COUNT(diagnosis_id) desc
)

A couple things wrong with this:

First, I'd recommend using a common table expression for the "top 5" lookup rather than a subquery - to me, it makes it a bit clearer, and though it doesn't matter here, it would likely perform better in a real work situation.

The main issue though is that you're ordering the top 5 lookup by the diagnosis id rather than the count. You'll need to do ORDER BY COUNT(diagnosis_id) instead.

select p.name from patients p
inner join (
    select top 5 diagnosis_id, count(*) as diagnosis_count
    from patients
    group by diagnosis_id
    order by diagnosis_count) t on t.diagnosis_id = p.diagnosis_id

try this:

SELECT name FROM patients
WHERE diagnosis_id IN
(SELECT TOP(5) diagnosis_id FROM patients
GROUP BY diagnosis_id
ORDER BY COUNT(diagnosis_id))