Below are the different ways to find the Nth highest salary of employee from employee table.
Replace 'n' by 2 to get 2nd highest salary of employee and replace by 3 to get 3rd highest salary of employee.
1) This method uses TOP clause.
SELECT TOP 1 Salary
FROM
(
SELECT DISTINCT TOP n Salary FROM Employee ORDER BY Salary DESC
)T
ORDER BY Salary
2) Second method uses CTE along with ranking function
WITH Salaries AS
(
SELECT *, DENSE_RANK () OVER(ORDER BY Salary DESC) as 'RowNum'
FROM Employee
)
SELECT * FROM Salaries
WHERE RowNum = n
3) This method makes use of aggregate functions MAX and COUNT
SELECT MAX(Salary) FROM Employee E1
WHERE 3 =
(
SELECT COUNT(DISTINCT Salary)
FROM Employee E2
WHERE E1.Salary <= E2.Salary
)
Please reply if you can think of any other ways to do the same stuff.
Replace 'n' by 2 to get 2nd highest salary of employee and replace by 3 to get 3rd highest salary of employee.
1) This method uses TOP clause.
SELECT TOP 1 Salary
FROM
(
SELECT DISTINCT TOP n Salary FROM Employee ORDER BY Salary DESC
)T
ORDER BY Salary
2) Second method uses CTE along with ranking function
WITH Salaries AS
(
SELECT *, DENSE_RANK () OVER(ORDER BY Salary DESC) as 'RowNum'
FROM Employee
)
SELECT * FROM Salaries
WHERE RowNum = n
3) This method makes use of aggregate functions MAX and COUNT
SELECT MAX(Salary) FROM Employee E1
WHERE 3 =
(
SELECT COUNT(DISTINCT Salary)
FROM Employee E2
WHERE E1.Salary <= E2.Salary
)
Please reply if you can think of any other ways to do the same stuff.
No comments:
Post a Comment