Menu
How to find fifth highest salary in a single query in SQL Server
656
Here is a way to do this task using dense_rank() function. Query : select * from( select ename, sal, dense_rank() over(order by sal desc)r from Employee) where r=&n; To find to the 2nd highest sal set n = 2 To find 3rd highest sal set n = 3 and so on.
SELECT MAX(SALARY) FROM Employee WHERE SALARY < (SELECT MAX(SALARY) FROM Employee); This query will give you the desired output i.e 12000, which is the second highest salary.
Best Answer select rnk, last_name, department_id, salary from ( select last_name, department_id, salary, RANK () OVER ( PARTITION BY department_id ORDER BY salary DESC ) AS rnk from employees ) where rnk <= 3 ; You still need a sub-query, because analytic functions are computed after the WHERE clause is applied.
In SQL Server 2005 & 2008, create a ranked subselect query, then add a where clause where the rank = 5.
select
*
from
(
Select
SalesOrderID, CustomerID, Row_Number() Over (Order By SalesOrderID) as RunningCount
From
Sales.SalesOrderHeader
Where
SalesOrderID > 10000
Order By
SalesOrderID
) ranked
where
RunningCount = 5
These work in SQL Server 2000
DECLARE @result int
SELECT TOP 5 @result = Salary FROM Employees ORDER BY Salary DESC
Syntax should be close. I can't test it at the moment.
Or you could go with a subquery:
SELECT MIN(Salary) FROM (
SELECT TOP 5 Salary FROM Employees ORDER BY Salary DESC
) AS TopFive
Again, not positive if the syntax is exactly right, but the approach works.
41
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
