As all of you aware, in most of the interview, regardless you are fresher or the experienced, in SQL section there is all time favorite question that is how to find nth highest salary.
most of us fail because, our brain wont work when they ask this type of question.
here I am going to solve the problem with steps you don't forget.
now think about about the columns required to calculate highest salary.
we need to have empno, ename and salary columns , right ?
ok, now think about the logic to get rank of salary.
now shall we use rank() or dense_rank ().
always remember, it is best to use dense_rank function.
now our initial query looks like this,
select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e ;
which returns values like this.
now, the RNK column has the rank values for which we can apply filter and get the highest salary details.
for example, if you want to see 3rd highest salary, apply filter on RNK column like this.
select * from (select e.empno,e.ename,sal,dense_rank() over (order by sal desc) as rnk from scott.emp e
) where rnk=3;
the output will be
Thanks and happy learning :)
