Second Highest Value and Top-N Records in MySQL

To get a second record after sorting, combine ORDER BY with LIMIT. If you need the second distinct value when ties are possible, use a subquery or DENSE_RANK().

Highest record

Sort by mark from high to low and return one row. Adding id as a tie-breaker makes row order deterministic when marks are equal.

SELECT id, name, class, mark
FROM student
WHERE class = 'Six'
ORDER BY mark DESC, id ASC
LIMIT 1;

Second row after sorting

MySQL offsets start at zero. Skip the first sorted row and return one row:

SELECT id, name, class, mark
FROM student
WHERE class = 'Six'
ORDER BY mark DESC, id ASC
LIMIT 1 OFFSET 1;

Top 3 records

SELECT id, name, class, mark
FROM student
WHERE class = 'Six'
ORDER BY mark DESC, id ASC
LIMIT 3;

Second distinct highest mark with a subquery

This version finds the next lower mark even when several rows are tied at the maximum:

SELECT MAX(mark) AS second_highest_mark
FROM student
WHERE class = 'Six'
  AND mark < (
      SELECT MAX(mark)
      FROM student
      WHERE class = 'Six'
  );

To return the complete student row or rows having that second distinct mark:

SELECT id, name, class, mark
FROM student
WHERE class = 'Six'
  AND mark = (
      SELECT MAX(mark)
      FROM student
      WHERE class = 'Six'
        AND mark < (
            SELECT MAX(mark)
            FROM student
            WHERE class = 'Six'
        )
  )
ORDER BY id;

Second distinct highest with DENSE_RANK()

On MySQL 8+, DENSE_RANK() is often the clearest method when you want ranking by distinct values.

WITH ranked AS (
    SELECT id, name, class, mark,
           DENSE_RANK() OVER (
               PARTITION BY class
               ORDER BY mark DESC
           ) AS mark_rank
    FROM student
)
SELECT id, name, class, mark
FROM ranked
WHERE class = 'Six'
  AND mark_rank = 2
ORDER BY id;

Second lowest record

Reverse the sort direction and skip the first row:

SELECT id, name, class, mark
FROM student
WHERE class = 'Six'
ORDER BY mark ASC, id ASC
LIMIT 1 OFFSET 1;

Which method should you use?

For large tables, index the columns commonly used for filtering and ordering, and make the ORDER BY deterministic when ties matter.

Subhendu Mohapatra

Author & Instructor at plus2net

I write and maintain practical tutorials on Python, PHP, SQL, JavaScript, HTML, jQuery, and web development at plus2net. The tutorials focus on clear explanations, working examples, and code that readers can test and adapt while learning.

About the author   Watch on YouTube

Connect: