Select * from emp
where sal=(Select max(sal) from emp
Where sal<(select max(sal) from emp)
);
select * From emp e
Where 2-1=(select count(distinct sal) from emp m where m.sal>e.sal);
or
select * From emp e
Where 2=(select count(distinct sal) from emp m where m.sal>=e.sal);
In place of 2 ,you can put 3(third highest salary), 4(third highest salary) … so on
Using dense_rank
Select * from
(select dense_rank() over(order by sal desc) as dr,e.* from emp e )
Where dr=2;
Using row_number
Select * from
(select row_number() over(order by sal desc) as rn,e.* from emp e )
Where rn=2;2nd highest salary output on tool
3rd highest salary
using densk_rank
Select * from
(select dense_rank() over(order by sal desc) as dr,e.* from emp e )
Where dr=3;Using row_number
Select * from
(select row_number() over(order by sal desc) as rn,e.* from emp e )
Where rn=3;This is the way to find out 2ng,3rd,4th … salary find out.
3nd highest salary output on tool
