How to Find Second Highest or maximum Salary of Employee in Sql Server

Suppose you want to get second highest salary or you want to find the highest salary.
Use following query to get small data in temp table.

select * into #tmpEmp

from

(select 'E1' EmpNo,'Emp1'Name,'Dept1' Dept,25000 Salary

union all

select 'E2' EmpNo,'Emp2'Name,'Dept1' Dept,2000 Salary

union all

select 'E3' EmpNo,'Emp3'Name,'Dept2' Dept,30000 Salary

union all

select 'E4' EmpNo,'Emp4'Name,'Dept2' Dept,2000 Salary

union all

select 'E5' EmpNo,'Emp5'Name,'Dept2' Dept,200000 Salary

union all

select 'E5' EmpNo,'Emp5'Name,'Dept2' Dept,15000 Salary)P

Result:

Now just set the Rank Column to the table data using below query.

Get the second highest salary from each department using this query:

select * from

(select ROW_NUMBER()Over(partition by Dept order by Salary desc)Rank,* from #tmpEmp)P

where Rank=2

Result:

Get highest salary from this query:

select * from

(select ROW_NUMBER()Over(order by Salary desc)Rank,* from #tmpEmp)P

where Rank=1

Result: