patternsqlMajor

Get second highest value in a table

Submitted by: @import:stackexchange-dba·Mar 10, 2026·

Viewed 0 times

valuegetsecondhighesttable

Problem

id value
1   50
2   60
3   55

select max(value) from tablename;

Generally we know, we will get 60, but I need the next value 55.

How do I get the value 55 using SQL?

Solution

To get the second highest distinct value in the table you can use

SELECT MIN(value)
FROM   (SELECT DISTINCT TOP (2) value
        FROM   tablename
        ORDER  BY value DESC)T
/*If only one distinct value return nothing. */
HAVING MIN(value) <> MAX(value);

Code Snippets

SELECT MIN(value)
FROM   (SELECT DISTINCT TOP (2) value
        FROM   tablename
        ORDER  BY value DESC)T
/*If only one distinct value return nothing. */
HAVING MIN(value) <> MAX(value);

Context

StackExchange Database Administrators Q#155998, answer score: 24

Revisions (0)

No revisions yet.