CODEWITHSUNDEEP
sqlsql-serverpostgresqllimit
Mahantesh. N. Hadimani.
Mahantesh. N. Hadimani.

Reputation: 597

How to select the second highest salary row without using limit function in SQL?

But the doubt is how do I get the data of second highest salary details, without using limit function. I have a restriction for limit.

Upvotes: 0

Views: 1877

Answers (4)

Venkataraman R
Venkataraman R

Reputation: 12959

There is one more approach.

SELECT  TOP  1 * 
from 
TableName 
WHERE Salary  <  (SELECT MAX(Salary) from  TableName )
ORDER BY Salary DESC

Upvotes: 1

Grzegorz Łyp
Grzegorz Łyp

Reputation: 1

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

Upvotes: 0

LukStorms
LukStorms

Reputation: 29667

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.

Upvotes: 4

The Impaler
The Impaler

Reputation: 48810

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

Upvotes: 7

Related Questions