MySQL customized join query using multiple tables

Posted by itgeek on Stack Overflow See other posts from Stack Overflow or by itgeek
Published on 2013-10-30T15:50:54Z Indexed on 2013/10/30 15:53 UTC
Read the original article Hit count: 177

Filed under:

I am searching one student from each class from one group. There are different class groups and every group has different classes and every class has multiple students. See below:

Group1 --> Class1, Class2 etc
Class1 --> GreenStudent1, GreenStudent2 etc
Class2 --> RedStudent1, RedStudent2 etc
------------------------------------------------------

SELECT 
   table1.id,
   table1.myname,
   table1.marks
   table2.studentid,
   table2.studentname
FROM table1
   INNER JOIN table3 ON table1.oldid = table3.id
   INNER JOIN table2 ON table2.studentid = table3.newid
WHERE 
   table1.classgroup = 'SCI79' 
GROUP BY table1.oldid
ORDER BY table1.marks DESC

There are different joins applied in the query. Above mentioned query giving me correct results but I need little modification in it. Current query returning me one student from each class.

What I need? I need one student from each class but only that student who has MAXIMUM

 table1.marks

So I should have one student from each class who has maximum number in their relevant classes. Can anyone suggest some solution or rewrite this query? Thanks :)

© Stack Overflow or respective owner

Related posts about mysql