mysql

Select second highest column value MySQL

You can find the second-highest value from a table column in MYSQL by ordering the column values in descending order and using LIMIT and OFFSET.

SELECT
    (SELECT DISTINCT
            column_name
        FROM
            table_name
        ORDER BY column_name DESC
        LIMIT 1 OFFSET 1
    ) AS second_highest_value
;

For Example, If there is a table named subject_scores, and we want to find the second-highest score from the column 'score'.

+----+--------+
| Id | score  |
+----+--------+
| 1  | 10     |
| 2  | 30     |
| 3  | 20     |
| 3  | 50     |
+----+--------+

To get the second-highest score from the above table the query will be

SELECT
   (SELECT DISTINCT
          score
       FROM
          subject_scores
       ORDER BY score DESC
       LIMIT 1 OFFSET 1
   ) AS second_highest_score
;

The output from the above table will be:

+----------------------+
| second_highest_score |
+----------------------+
| 20                   |
+----------------------+

Was this helpful?