Select Max(Salary) from tblEmployee A (Nolock) where n=(select count(distinct(Salary)) from tblEmployee B (Nolock) where B.Salary>=A.Salary)
Select Max(Salary) from tblEmployee A (Nolock) where n=(select count(distinct(Salary)) from tblEmployee B (Nolock) where B.Salary>=A.Salary)
We can get nth highest salary using Common Table Expression (CTE).
WITH SalaryDenseRank As
(
SELECT *,
DENSE_RANK() OVER (ORDER BY Salary DESC) as Rank
FROM tblEmployee (Nolock)
)
SELECT *
FROM SalaryDenseRank
WHERE Rank = n
We can also get nth highest salary using Common Table Expression (CTE) in SQL SERVER.
WITH MAXSALAY_CTE
AS
(
Select Max(Salary) as MaxSalary from Employee A (Nolock)
where n=(select count(distinct(Salary)) from Employee B (Nolock)
where B.Salary>=A.Salary)
)
SELECT * FROM MAXSALAY_CTE