Get Max record from query SQL Server

Question

I have the following tables

CREATE TABLE Staff
(
    staffID int,
    fullName varchar(100) NOT NULL,
    s_category varchar(25),
    s_email varchar(50),
    s_contactNo int,
    speciality varchar(100),
    qualifications varchar(250),
    pre_employment varchar(200),
    salary numeric(8,2),
    staff_gender char(1),
    staff_joined_date datetime,
    branch_allocated int,

    CONSTRAINT PK_Staff 
        PRIMARY KEY (staffID),
    CONSTRAINT FK_Staff_Branch 
        FOREIGN KEY (branch_allocated) REFERENCES Branch(branchID) 
                ON DELETE CASCADE,
    CONSTRAINT CHK_StaffGender CHECK (staff_gender='M' OR staff_gender='F'),
    CONSTRAINT CHK_FullName CHECK (fullName NOT LIKE '%[^A-Za-z ]%'),
    CONSTRAINT CHK_SALARY CHECK (salary>0 AND salary<=150000)
);

CREATE TABLE Appointment
(
    appID int,
    patientId int,
    staffId int,
    appDateTime DateTime,

    CONSTRAINT PK_Appointment PRIMARY KEY (appID),
    CONSTRAINT FK_Appointment_Patient 
        FOREIGN KEY (patientId) REFERENCES Patient(patientID) 
                ON DELETE CASCADE,
    CONSTRAINT FK_Appointment_Staff 
        FOREIGN KEY (staffId) REFERENCES Staff(staffID) 
                ON DELETE CASCADE,
    CONSTRAINT CHK_AppointmentDate CHECK (appDateTime>=GETDATE())
);

I want to get the doctor(s) with maximum number of patients.

I have created the query as follows.

SELECT 
    s.staffID AS 'ID',s.fullName AS 'Name', COUNT(a.appID) AS 'Number of Patients'
FROM 
    Staff s 
INNER JOIN 
    Appointment a ON s.staffID = a.staffId
GROUP BY 
    s.staffID, s.fullName
ORDER BY 
    'Number of Patients' DESC

But this returns all doctors. Can you help me to find the doctor with the largest number of patients?

Tim Biegeleisen · Accepted Answer · 2018-03-30 07:27:23Z

2

WITH cte AS (
    SELECT
        s.staffID AS ID,
        s.fullName AS Name,
        COUNT(a.appID) AS [Number of Patients],
        DENSE_RANK() OVER (ORDER BY COUNT(a.appID) DESC) AS rank
    FROM Staff s
    LEFT JOIN Appointment a
        ON s.staffID = a.staffId
    GROUP BY s.staffID, s.fullName
)

SELECT
    ID, Name, [Number of Patients]
FROM cte
WHERE rank = 1;

edited Mar 30, 2018 at 7:27

Tim Biegeleisen

526k32 gold badges324 silver badges399 bronze badges

answered Mar 30, 2018 at 7:22

Nishant Gupta

3,6761 gold badge13 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Nishant Gupta Over a year ago

@TimBiegeleisen Thanks for the suggestion.

BMErEr · Accepted Answer · 2018-03-30 07:26:41Z

1

Can you try this

SELECT TOP number|percent column_name(s)
FROM table_name
WHERE condition;

on your situation SELECT TOP 1

answered Mar 30, 2018 at 7:26

BMErEr

15114 bronze badges

1 Comment

Tim Biegeleisen Over a year ago

Or use TOP 1 WITH TIES to simulate rank.

Collectives™ on Stack Overflow

Get Max record from query SQL Server

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related