Get second highest value in a table

Question

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?

Answer 1

Assuming the highest value only occurs once, another way would be to use OFFSET (SQL Server 2012 or later):

SELECT * 
FROM tablename
ORDER BY column DESC 
OFFSET 1 ROW 
FETCH NEXT 1 ROW ONLY;

Answer 2

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);

Answer 3

A generic solution can be like below:

;WITH CTE AS
(
    SELECT
        Col1
        , Col2
        , <AnyColumns>
        , ROW_NUMBER() OVER (ORDER BY <AnyColumns>) AS RowNum
    FROM <YourTable>
    WHERE <YourCondition>
)
SELECT *
FROM CTE
WHERE RowNum = 2 -- Or any condition which satisfies your problem

Here you can also define the range like RowNum >= 10 AND RowNum <= 20. And it will give you 10th to 20th rows with all required columns.

Answer 4

You have the usual top trick such as:

select top 1 *
from (
    select top 2 *
    from my_table
    order by value desc
    ) t 
order by value asc 

Or you can also use CTE like:

with CTE as
(
select value, ROW_NUMBER() over(order by value desc) as ord_id
from my_table
)
select value
from CTE
where ord_id = 2

Or, if you use recent version of SQLServer (>= 2012), the lag function.

SELECT  top 1  lag(value, 1,0) OVER (ORDER BY value)  
FROM my_table
order by value desc

Answer 5

I will do like this:

SELECT MAX(value)
FROM tablename
WHERE value < (SELECT MAX(value)
               FROM tablename)

Answer 6

You can use the ROW_NUMBER() windowing function as well. If you want to get the 2nd entry when ordered by your target value, you can do:

SELECT value 
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY value DESC) as RN,
        value
    FROM my_table
) d
WHERE RN = 2

Now if you want to get the 2nd highest value and you have duplicates, you might want to group by the value entry so that you only get distinct values.

SELECT value 
FROM (
    SELECT 
        ROW_NUMBER() OVER (PARTITION BY NULL ORDER BY value DESC) as RN,
        value
    FROM my_table
    GROUP BY value
) d
WHERE RN = 2

You should be able to modify this approach to include a MIN(id) in the inner select if you need to know the ID of the first record with the 2nd highest value (assuming you had a data set with two 60s and two 55s)