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?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.
Sign up to join this communityid 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)
ROW_NUMBER()
with DENSE_RANK()
- you also get all the other columns for free. No need to use GROUP BY
.
– ypercubeᵀᴹ
Nov 23 '16 at 13:16