How to use rownum [duplicate]

Question

This question already has an answer here:

• How to get second largest or third largest entry from a table 12 answers
• SELECTing top N rows without ROWNUM? 5 answers

I have a employee table in oracle with name,salary and other details.

I am trying to get the second highest salary but not able to fetch.

This one working fine

with e_salary as (select distinct salary from employee)
select salary from e_salary
order by salary desc

And gives output:

450000

61000

60000

50000

40000

30000

20000

6000

but when i am using the same query to fetch second highest row not getting any output

select salary
  from ( with e_salary as (select distinct salary from employee)
         select salary from e_salary order by salary desc)
 where rownum = 2

but as i replace the rownum=2 with rownum<2 it gives output of first two records. Please someone explain why rownum=2 is not working

Answer 1

select ename  ,sal  ,rank() over (order by sal desc) ranking from emp;

Try this one.

Follow this link, all the things regarding nth highest row is given over here in oracle:

http://www.oratable.com/nth-highest-salary-in-oracle/