MySQL Nth Highest Value
A common interview question: find the 2nd, 3rd or Nth highest marks. We use the students table with the marks 85, 92, 78, 88, 65 and 45.
Third highest marks
SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2;
Tutorials Class - Output Window
| marks |
|---|
| 85 |
For the Nth highest value, use OFFSET N-1. So the 3rd highest uses OFFSET 2, the 2nd highest uses OFFSET 1. DISTINCT makes sure that same marks are counted one time.
Get the student with that marks
SELECT name, marks
FROM students
WHERE marks = (
SELECT DISTINCT marks
FROM students
ORDER BY marks DESC
LIMIT 1 OFFSET 2
);
Tutorials Class - Output Window
| name | marks |
|---|---|
| Robin | 85 |
Note:
If there are fewer than N different values, the subquery returns nothing and the result is empty (NULL). Also, more than one student can have the same marks, and all of them are shown.
Related: MySQL LIMIT, DISTINCT and Subqueries.
Learn more about the similar topics:
| Tutorials |
|---|
| No Content Found. |
| Exercises & Assignments |
|---|
| No Content Found. |
| Interview Questions & Answers |
|---|
| No Content Found. |