[SOLVED] Find student id with highest marks using INNER JOIN and EXCEPT?

Issue

Given a table, find the highest marks using INNER JOIN and EXCEPT. It straight forward find marks. Select max(marks) from Students. But how to find highest using INNER JOIN and EXCEPT?

Students Table

Solution

You don’t need to write an INNER JOIN or EXCEPT (your question is tagged mysql which doesn’t support the EXCEPT statement) to get the student id with the highest mark. It’s much simpler to write:

select sno from students
where marks = (select max(marks) from students)

Result:

3

If you use an INNER JOIN you just complicate things and essentially do the same. Example:

select s1.sno, s1.marks from students s1
inner join (select max(marks) as max_mark from students) s2
on s1.marks = s2.max_mark

Answered By – Adrian

Answer Checked By – Willingham (BugsFixing Volunteer)