Mysql Max query

Question

I have two tables :

teachers (teacher_id,teacher_name)
courses (teacher_id,course_id)

And I need to display names of the teachers, teaching maximum number of courses :

mysql> select teachers.teacher_name,tmp1.teacher_id,tmp1.cnt from (select max(tm p.cnt) as tmpMax from (select teacher_id,count(teacher_id) as cnt from courses g roup by teacher_id) as tmp) as tmp2,(select teacher_id,count(teacher_id) as cnt from courses group by teacher_id) as tmp1,teachers where tmp1.cnt = tmp2.tmpMax and teachers.teacher_id = tmp1.teacher_id;

I came up with the above query. Is there a simpler query for this problem?

Answer 1

SELECT teacher_id, teacher_name, COUNT(1) AS course_count
FROM teachers
WHERE course_count = (SELECT MAX(COUNT(1)) FROM courses GROUP BY teacher_id)
GROUP BY teacher_id

Answer 2

Give this a shot:

select a.teacher_name as 'Teacher', count(b.course_id) as 'Num of Courses'
from teachers a
inner join courses b on a.teacher_id = b.teacher_id
group by b.teacher_id
order by count(b.course_id) desc

edit The following will give you the exact same result as your query:

select a.teacher_name as 'Teacher', a.teacher_id as 'Teacher Id', count(b.course_id) as 'Num of Courses' 
from teachers a 
inner join courses b on a.teacher_id = b.teacher_id 
group by b.teacher_id 
order by count(b.course_id) desc 
limit 1

Answer 3

This should work:

select teacher_name
from teachers
where teacher_id IN
(
    select t.teacher_id
    from teachers t inner join courses c on t.teacher_id = c.teacher_id
    group by t.teacher_id
    having count(*) = 
    (
        select max(courses_per_teacher) from
        (
            select teacher_id, count(*) as courses_per_teacher
            from teachers t inner join courses c on t.teacher_id = c.teacher_id
            group by teacher_id
        )
    )
)

Logic in pseudo-code:

• Find the teacher names whose IDs are in -->
• The group of teachers whose number of courses is -->
• Equal to the maximum number of -->
• The list of numbers of courses per teacher

Hope that helps.