The challenge is to find the employees with the second highest salary in each department. However, it is a little more complicated because if two employees have the same salary, you need to list both of them.
with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname) select * from CTE where row=2
create table empname ( name varchar(20), salary int, deptno int ) Data in table inthiyaaz 2000 10 khaja 8000 10 Afroz 2000 20 Heera 1000 30 Naseeb 1000 10 King 1000 20 Imroz 3000 30 Naseer 2000 10 Pankaj 1500 30 with CTE as(select row_number() over(partition by deptno order by salary desc) row,salary,deptno from empname) select * from CTE where row=2
Tags: sql,department wise salary
nice bhai
will not work :) Row number function enumerates regardless data are even (here we have such example in the test data)