Skip to main content

Posts

Showing posts with the label partition

How to write query second highest salary in for Employee in SQL

To write query second highest salary in for Employee in SQL there are many way to achieve this I am sharing here some sample examples. Select * from(select deptno,sal,dense_rank() over (partition by deptno order by sal desc)r from emp) where r=2 select empno,ename,sal,deptno, row_number() over(partition by deptno order by sal desc)rn from emp )select * from cte where rn=2 Select max(sal)from emp  Where sal <(select max(sal) from emp) Select deptno, max(salary) from(Select a.deptno,a.salary from employee a, (Select deptno,max(salary) from employee group by deptno) b where a.deptno=b.deptno And a.salary<b.salary) group by deptno;