Second highest value among column values of same row in SQL

Question

Sample data:

id score1 score2 score3 score4
1  10     05      30    50
2  05     15      10    00
3  25     10      05    15

Expected result set:

id col_value
1    30
2    10
3    15

Answer 1

Use a CASE expression to tell which score to omit in your GREATEST() call.

SELECT id,
    CASE GREATEST(score1, score2, score3, score4)
        WHEN score1 THEN GREATEST(score2, score3, score4)
        WHEN score2 THEN GREATEST(score1, score3, score4)
        WHEN score3 THEN GREATEST(score1, score2, score4)
        ELSE GREATEST(score1, score2, score3)
    END AS col_value
FROM your_table ;

This solution generalizes easily to any number of columns.

---

and a variation without CASE, using both GREATEST() and LEAST():

SELECT id,
    LEAST(
        GREATEST(score1, score2, score3),
        GREATEST(score2, score3, score4),
        GREATEST(score3, score4, score1),
        GREATEST(score4, score1, score2)
    ) AS col_value
FROM your_table ;