How to select second row in sql without using limit?-CodePudding

Let's say I have a table with list of employee name and salary, how to get second highest salary person's data? without using limit function.

CodePudding user response：

You can use ROW_NUMBER(). For example

select *
from (
  select *, row_number() over(order by salary desc) as rn 
  from employee
) x
where rn = 2

CodePudding user response：

Starting with MS Sql Server 2012 you may use OFFSET as part of the ORDER BY syntax.

SELECT emp.*
FROM Employees emp
ORDER BY emp.Salary DESC
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY

The offset 1 skips the top 1, so the fetch only returns the 2nd top.

CodePudding user response：

This is not a good habid to assign to variable more than once, but it works and do not need any risky window function.

select top 2 @salary=salary
  from employee
where employeeid = @id
order by salary desc