CODEWITHSUNDEEP
mysqlsqljoinmax
Djaouad
Djaouad

Reputation: 22766

Select rows with max value (value is generated by join)

I have a database that can be summarized like this:

teacher (tid, f_name, l_name);
subject (sid, title);
teacher_subject (tid, sid);

What I want is to get the teachers that teach the most subjects, I have seen some similar but not duplicate questions here and couldn't patch up the solutions to get to what I want, this is in short what I've written:

select max(num_subs) from
   (select t.f_name, t.l_name, count(t.tid) num_subs
    from teacher t
    join teacher_subject ts
    on t.tid = ts.tid
    group by t.tid)
max_subs;

But couldn't go any further. I'm sure there's a way to it as I was sometimes getting too close to it but never reached.

Upvotes: 2

Views: 43

Answers (1)

Thorsten Kettner
Thorsten Kettner

Reputation: 94884

This is a little awkward in MySQL for the lack of window functions or a limit clause that allows for ties, but here you go:

select *
from teacher 
where tid in
(
  select tid
  from teacher_subject
  group by tid
  having count(*) =
  (
    select count(*)
    from teacher_subject
    group by tid
    order by count(*) desc
    limit 1
  )
);

Just for the record, in standard SQL this is merely:

select *
from teacher t
order by (select count(*) from teacher_subject ts where ts.tid = t.tid) desc
fetch first 1 row with ties;

Upvotes: 2

Related Questions