Real Time SQL Interview Queries|| 2nd Highest Salary in Each Dept

[@KomalTyagi-p4o]

Nice,👌

[@madhusudhanreddyt2838]

-- using the common table expressions
;with cte_employee as
(
select
e.empid, e.salary,e.deptid, d.deptname,
ROW_NUMBER() OVER (PARTITION BY e.deptid ORDER BY e.salary DESC) as RNo
from employee as e inner join department as d
on e.deptid = d.deptid
)
select
empid, salary, deptid, deptname
from cte_employee
where cte_employee.RNo = 2;
-- using the derived table concept
select
empid, deptid, deptname, salary
from
(
select
emp.empid, emp.salary, emp.deptid, dept.deptname, ROW_NUMBER() OVER (PARTITION BY dept.deptname ORDER BY emp.salary DESC) as RNo
from employee as emp inner join department as dept
on emp.deptid = dept.deptid
) as temp
where temp.RNo = 2;
Thank you