2nd Highest Salary using max function:
- select MAX(ID) from Employees where ID NOT IN (Select MAX(ID) from Employees)
- select MAX(ID) from Employees where ID < (Select MAX(ID) from Employees )
Using Top we can retrieve nth highest salary:
- select top 1 ID from (select top 2 ID from Employees order by id desc) as tblEmployee order by ID ASC
-
- Using dense_rank to retrieve 2nd Highetst salary if we have duplicate rows:
-
- with CTETable
- as
- (
- select ID, Name, DENSE_RANK() over (order by ID desc) as Rnk from Employees
- )
- select Name, ID from CTETable where rnk=2