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