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;
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);
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
I will do like this:
SELECT MAX(value) FROM tablename WHERE value < (SELECT MAX(value) FROM tablename)
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)
Thank you for your interest in this question.
Because it has attracted low-quality or spam answers that had to be removed, posting an answer now requires 10 reputation on this site (the association bonus does not count).
Would you like to answer one of these unanswered questions instead?