Different ways to find out N-th largest salary, Below example demonstrate 3 rd largest salary. Replace 3 with 'N'
Option 1:
- SELECT TOP 1 salary FROM (
- SELECT TOP 3 salary
- FROM employees
- ORDER BY salary DESC) AS emp
- ORDER BY salary ASC
Option 2 (Using CTE:)- WITH CTE AS
- (
- SELECT salary,
- RN = ROW_NUMBER() OVER (ORDER BY salary DESC)
- FROM employees
- )
- SELECT salary
- FROM CTE
- WHERE RN = 3
Option 3: - SELECT MIN(Salary)
- FROM employees
- WHERE Salary IN(SELECT TOP 3 Salary FROM employees ORDER BY Salary DESC)