CODEWITHSUNDEEP
mysqlsql
nbhatti2001
nbhatti2001

Reputation: 363

Get list of students with max number in each class

I want to get the list of students from db, who have get maximum number in each class with student name. Using MySQL DB.

I have the following tables like Student, classes, result (with different years results)

Tables structure student(student_id,student_name,class_id,address), class(class_id,class_name), results(result_id,student_id,year,marks)

and I need list like 

Student Name   class   Marks
Jon            A-1      800
Steve          B-1      789

Upvotes: 2

Views: 3364

Answers (3)

Yaroslav
Yaroslav

Reputation: 6534

Edit corrected code, comment was correct

Try the code on this SQL Fiddle link

Upvotes: 4

Sameer
Sameer

Reputation: 4389

select s1.student_name, c1.class_name, r1.marks 
from student s1, class c1, results r1,
  (select s2.class_id, max(r2.marks) marks 
   from results r2, student s2
   where r2.student_id = s2.student_id 
   group by s2.class_id) agg
where r1.marks      = agg.marks 
  and r1.student_id = s1.student_id
  and s1.class_id   = c1.class_id
  and s1.class_id   = agg.class_id

Upvotes: 1

Andomar
Andomar

Reputation: 238076

You could use a subquery to filter the students with the highest mark per class:

select  s.student_name
,       c.class_name
,       r.marks
from    results r
join    student s
on      r.student_id = s.student_id
join    class c
on      c.class_id = s.class_id
where   r.result_id =
        (
        select  r2.result_id
        from    student s2
        join    results r2
        on      s2.student_id = r2.student_id
        where   c.class_id = s2.class_id
        order by
                r2.marks desc
        limit   1
        )

Live example at SQL Fiddle.

Upvotes: 3

Related Questions