nth highest salary for each department from employee table

 If you need 3rd highest salary for each department from employee table

Approach 1:
 Declare n int
set n=3
select * from employee e1 where (n-1)=(select count(*) from where employee where e1.Salary<salary and e.depid=depid)


 If you need 3rd highest salary from employee table
 Approach 2:  
 select top 1 salary from
 ( select distinct top 3 salary from  employee order by salary desc)s
order by salary asc



We can do with more ways....with row_number,rank, temp table with add identity column

select name,salary,depid from
(
select *, row_number() over (partition by depid order by salary desc) as sno
from employee
) as t
where sno=3