Top counts 1

[Eddyoftheyear]

Hi All,

I need to pull the top six provider who has the highest number of apppointments from each depts in the following statement:

SELECT Query2.Depts, Query2.[Full Name], Query2.CountOfAppointment
FROM Query2;

 

[PHV]

What about this ?

SELECT A.Depts, A.[Full Name], A.CountOfAppointment
FROM Query2 A INNER Query2 B ON A.Depts=B.Depts AND A.CountOfAppointment<=B.CountOfAppointment
GROUP BY A.Depts, A.[Full Name], A.CountOfAppointment
HAVING COUNT(*)<=6
ORDER BY 1, 3 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Eddyoftheyear]

I tried and gave me syntax error On query2

 

[PHV]

Sorry for the typo:

SELECT A.Depts, A.[Full Name], A.CountOfAppointment
FROM Query2 A INNER [!]JOIN[/!] Query2 B ON A.Depts=B.Depts AND A.CountOfAppointment<=B.CountOfAppointment
GROUP BY A.Depts, A.[Full Name], A.CountOfAppointment
HAVING COUNT(*)<=6
ORDER BY 1, 3 DESC

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Eddyoftheyear]

THANK YOU SOO MUCH. IT WORKED!!