SQL top + count() confusion

Question

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?

Answer 1

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

Answer 2

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
)

Answer 3

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))

Answer 4

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.