Write a query to show the student name who has the second highest marks

Description: Show the name and marks of the student with the second highest marks.

Tables: the exercises use the students (id, name, city, marks, course_id) and courses (id, course_name) tables, with the data from our MySQL INSERT chapter. The students are: Robin (Delhi, 85), Maria (Mumbai, 92), John (Pune, 78), Asha (Jaipur, 88), Vikram (Delhi, 65), Neha (Mumbai, 45).

Hint:

• The second highest value is the highest value that is smaller than the maximum.
• Use a subquery inside a subquery.

---

MySQL query for the second highest marks

SELECT name, marks
FROM students
WHERE marks = (
  SELECT MAX(marks)
  FROM students
  WHERE marks < (SELECT MAX(marks) FROM students)
);

Output:

How it works

• The innermost query finds the maximum marks: 92.
• The middle query finds the maximum of the marks that are smaller than 92: 88.
• The outer query returns the student with 88 marks.
• Another way is ORDER BY marks DESC LIMIT 1 OFFSET 1.

Read MySQL Subqueries.

• Previous Exercise
• Next Exercise

---

Tutorials
No Content Found.

Exercises & Assignments
No Content Found.

Interview Questions & Answers
No Content Found.