CODEWITHSUNDEEP
sql-serverdatabase
Sami
Sami

Reputation: 486

SQL to find maximum value and then make a decision, which name has the greatest value

I am trying to find "which instructor is taking the maximum number of courses during Fall 2017".

I know I have to use the max function in it, Also I have to count and compare which instructor has the maximum value. but I don't know how to. I have written some part of the query which is

SELECT DISTINCT firstname, lastname
FROM Faculty F 
INNER JOIN CourseSection C ON F.FacID = C.FacID

The data of Faculty table is

enter image description here

And CourseSection table is

enter image description here

Upvotes: 2

Views: 246

Answers (4)

Giorgos Altanis
Giorgos Altanis

Reputation: 2760

Can you try something like this?

SQL Server:

SELECT TOP 1 F.firstname, F.lastname
FROM Faculty F 
JOIN (
    SELECT FACID, COUNT(*) CNT FROM CourseSection GROUP BY FACID
) C
ON F.FacID = C.FacID
ORDER BY C.CNT DESC

MySql:

SELECT F.firstname, F.lastname
FROM Faculty F 
JOIN (
    SELECT FACID, COUNT(*) CNT FROM CourseSection GROUP BY FACID
) C
ON F.FacID = C.FacID
ORDER BY C.CNT DESC
LIMIT 1

ALLOW TIES

This is a SQL Server code (using a Common Table Expression) to allow selection of multiple instructors in case of ties:

; with t as (
select FacID, COUNT(*) CNT
from CourseSection
group by FacID
    )
select F.FirstName, F.LastName, t.CNT from t join Faculty F on F.FacID = t.FacID
where cnt = (select max(cnt) from t)
;

Upvotes: 3

fqhv
fqhv

Reputation: 1201

You can GROUP and take the TOP 1 to get the max.

SELECT FirstName, LastName
FROM Faculty
WHERE FacID = (
    SELECT TOP 1 FacID
    FROM CourseSection
    GROUP BY FacID
    ORDER BY COUNT(*) DESC )

The subquery in the WHERE clause will get the FacID of the faculty member who took the most courses. 

SELECT TOP 1 FacID     --Take just the top 1 record as that is the max
FROM CourseSection
GROUP BY FacID         --Group by the FacID to get a count of courses per FacID
ORDER BY COUNT(*) DESC --Order by the number of courses descending so the max is at the top

Since this subquery will return a single record it can be used in a comparison statement, so we simply put it in the the WHERE clause.

This will throw an exception though if the CourseSection table is empty and the subquery returns no results. You could prevent this by instead using an IN statement, even if it is less expressive.

WHERE FacID IN ( ... )

Upvotes: 1

Steve Lovell
Steve Lovell

Reputation: 2564

I've not tested this, so I may have made a typo somewhere, but the following logic should be right, and will give all the Faculty members involved in any tie.

select
    F.FacID, 
    F.FirstName,
    F.Lastname 
from
    Faculty F
    JOIN (
         select top 1 count(1) as MaxClasses
         from  CourseSection cs1
         group by cs1.FacID
         order by count(1) desc) as MaxClasses
    JOIN CourseSection cs2 ON
         F.FacID = cs2.FacID
group by
    F.FacID, 
    F.FirstName,
    F.Lastname
having
    count(1) = MaxClasses.MaxClasses

Upvotes: 1

user1327961
user1327961

Reputation: 452

SELECT TOP 1 F.firstname, F.lastname
FROM Faculty F 
JOIN (
    SELECT FACID, COUNT(*) CNT FROM CourseSection GROUP BY FACID
) C
ON F.FacID = C.FacID
ORDER BY C.CNT DESC

Upvotes: 2

Related Questions