Find duplicate maximum entry in SQL

Question

Ahoi everyone

I'm really new to SQL and have some homework for Uni to do.

I have got the following two tables:

Professors with:

ID, Name

and Assistans with:

ID, Name, Boss (foreign key)

My task is to find the professor with the most assistants.

What I have so far:

select top 1 
    p.name, 
    count (a.name) as NumberOfAssistants 
from 
    Professors p, Assistants a
where 
    a.Boss = p.ID
group by 
    name
order by 
    NumberOfAssistants

My problem is, that there are two professors with the same and maximum amount of Assistants. So Top 1doesn't work for me.

I'm kinda desperate as I cant wrap my head around this. Hope you can help me.

Thanks in advance

I would suggest using the ANSI syntax for the join too http://stackoverflow.com/questions/1599050/ansi-vs-non-ansi-sql-join-syntax — Dane, Nov 27 '15 at 16:23
[Bad habits to kick : using old-style JOINs](https://sqlblog.org/2009/10/08/bad-habits-to-kick-using-old-style-joins) - that old-style *comma-separated list of tables* style was replaced with the *proper* ANSI `JOIN` syntax in the ANSI-**92** SQL Standard (**more than 20 years** ago) and its use is discouraged — marc_s, Nov 27 '15 at 16:24
I would also suggest checking resources online as well as the course learning material. I recently graduated university to find that some of the lessons were slightly outdated. — Dane, Nov 27 '15 at 16:26
Thank you @GordonLinoff, works like a charm. Dane and marc_s I'll have a look into that, I found it easier to write this way. But I will change my bad habits. ;) Thanks to you too! — Tom K., Nov 27 '15 at 16:26
I don't think your teacher will be very happy about that 'top with ties', as this kind of University homework are usually made to develop your logic, not your knowledge on some programming language. But that's just my opinion. — Gabriel Rainha, Nov 27 '15 at 17:58

score 0 · Answer 1 · answered Nov 27 '15 at 16:26

0

maybe you can get the biggest number and using having to get what you want.

try:

select p.name, count(a.name) as AmountOfAssistants from Professors p, Assistants a where a.Boss = p.ID group by name having AmountOfAssistants = (select count(a.name) as AmountOfAssistants from Professors p, Assistants a where a.Boss = p.ID group by name order by AmountOfAssistants desc limit 1);

answered Nov 27 '15 at 16:26

Fujiao Liu

2,195
2
24
28

That's not sql server syntax, and is equivalent to what the OP already has (`TOP 1`) – Jamiec Nov 27 '15 at 16:26
That wouldnt solve my problem as it also gives out only the first entry. Solution is in the comments above. But thank you anyway. :) – Tom K. Nov 27 '15 at 16:28
@Jamiec, my mistake, I thought it is mysql. The sql get all professors with the most assistants, it is not equivalent to OP's output – Fujiao Liu Nov 27 '15 at 16:30

score 0 · Answer 2 · answered Nov 28 '15 at 14:12

/*
create table Professors ( ID varchar(1), Name varchar(10) )
go

insert into Professors values  ('1', 'Prof 1')
insert into Professors values  ('2', 'Prof 2')
insert into Professors values  ('3', 'Prof 3')
go

create table Assistants ( ID varchar(1), Name varchar(10), Boss varchar(1) )
go

insert into Assistants values ('1', 'Assist 1', '1')
insert into Assistants values ('2', 'Assist 2', '1')
insert into Assistants values ('3', 'Assist 1', '2')
insert into Assistants values ('4', 'Assist 1', '2')
insert into Assistants values ('5', 'Assist 1', '3')
go
*/

select p.ID, p.Name
from 
Professors p
inner join Assistants a on p.ID = a.Boss
group by p.ID, p.Name
having
count(a.ID) = 
(
    select max(NumAssistants)
    from (
        select Boss, count(ID) as NumAssistants
        from Assistants
        group by Boss
    ) a

)

Find duplicate maximum entry in SQL

2 Answers2