Kam Kam - 4 months ago 12
SQL Question

Sql issue of selecting max value from a grouped column

Hi guys actually I have a problem .

select * from Insurance_Plan;
select * from Enroll;
select * from Patient;


These three are the tables in my database. Now I have to the get the insurance plan with the most patient enrollees, and for that plan, its name, required copayment amount, and the number of patient enrollees.

I have done that part till here:

Select I.Insurance_Name,Max(I.Insurance_Copayment_Amount) As
Copayment_Amount,count(P.Patient_ID) As Number_Of_Patients
From Insurance_Plan as I inner Join
Enroll AS E on I.Insurance_Plan_ID=E.Insurance_Plan_ID
inner join Patient as P on P.Patient_ID=E.Patient_ID
group by Insurance_Name


As a result I am getting the output:
enter image description here

Now I want to show only the insurance plan with max number of patients enrolled which is the second row in the output shown. I am trying to use Max function but getting errors. It has to be done in a single efficient query.

Answer
 Select top (1) I.Insurance_Name,Max(I.Insurance_Copayment_Amount) As 
   Copayment_Amount,count(P.Patient_ID) As Number_Of_Patients
   From Insurance_Plan as I inner Join 
   Enroll AS E on I.Insurance_Plan_ID=E.Insurance_Plan_ID
   inner join Patient as P on P.Patient_ID=E.Patient_ID
   group by Insurance_Name
   order by count(P.Patient_ID) desc