Finding the Nth Highest Salary in Sql Server

vishalneeraj-24503
Posted by vishalneeraj-24503 under Sql Server category on | Points: 40 | Views : 480
Let's us Consider Employee_Master table it contains Columns Namely: Emp_ID,First_Name,Last_Name, and Salary

We will write a CTE(Common Table Expression) to find 2nd highest salary:-

WITH Employee_Dtls AS
(
SELECT SALARY,
DENSE_RANK() OVER(ORDER BY SALARY DESC) AS DENSERANK
FROM EMPLOYEE_Master
)

SELECT TOP 1 SALARY
FROM Employee_Dtls
WHERE DENSERANK = 2

Comments or Responses

Posted by: Bandi on: 9/8/2014 Level:Platinum | Status: [Member] [MVP] | Points: 10
If we want the employee details along with 2nd highest salary we can tweet your query for that by simply adding * next to the SELECT Top 1 SALARY.

What if any of the 2 employees having same salary which is 2nd highest salary?


Login to post response