Friday, June 25, 2010

MySql Max Query

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?

Part 2 : What does count(1) stand for?

Answer :
SELECT teacher_name, x.crs as courses
from teachers a join
(select teacher_id, count(*) crs from courses group by 1) x
on x.teacher_id = a teacher.id
order by 2 desc
LIMIT 1;

No comments:

Blog Archive