current community

your communities

more stack exchange communities

company blog
Stack Exchange Inbox Reputation and Badges
tour help
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. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top
up vote 1 down vote favorite
1
 
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?

share|improve this question
3  
What is the logic though? What defines "next"? The id? – Philᵀᴹ 6 hours ago
    
Why dont you first get the id for max value then +1 in it then get the value of this id? – TAHA SULTAN TEMURI 2 hours ago
up vote 4 down vote accepted

Another way would be to do offset (SQL Server 2012 or later):

SELECT * FROM tablename
ORDER BY column desc OFFSET 1 ROW FETCH NEXT 1 ROW ONLY;
share|improve this answer
6  
You are assuming that the highest value only occurs once. – Martin Smith 4 hours ago
    
thanks for your solution – Mahfuz Morshed 3 hours ago
up vote 5 down vote

To get the value from the highest row as ordered by id you can use

SELECT TOP (1) value
FROM   tablename
ORDER  BY id DESC;

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

SELECT TOP (1) value
FROM   (SELECT DISTINCT TOP (2) value
        FROM   tablename
        ORDER  BY value DESC)T
ORDER  BY value ASC
share|improve this answer
    
thanks for your solution – Mahfuz Morshed 3 hours ago
up vote 2 down vote

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 column.

share|improve this answer
up vote 1 down vote

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
share|improve this answer
    
thanks for your solution – Mahfuz Morshed 3 hours ago

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.