SQL-Query to find third highest

How to find third highest salary from emp table in Oracle.?

 

Code:

select sal from(select sal from
           (select distinct sal from emp order by sal desc
            where rownum<=3)  order by sal asc)
    where rownum=1;

 

Hi,
Thanks for your reply.I appreciate that.
I tried that Query in SQL and it gives the following error:

ERROR at line 3:
ORA-00907: missing right parenthesis

Could you please help further, as i think all parenthesis are closed.
Thanks
javapank

 

Try this..itz working fine..

Code:

select sal from (select sal from (select distinct sal from ( select distinct sal from emp order by sal desc) where rownum<=3) order by sal asc) where rownum<=1;

 

Thanks for your answer.I did little modification and still it works with the query:
select min(sal) from ( select distinct sal from emp order by sal desc) where rownum<=3

Let me know that will this query is perfect for all the kinds of data in the table.
Thanks
Java Pank

 

if you want to find 3rd highest or nth highest then do the following query.. just try this n let me know..

select * from emp e
where (&n-1)=(select COUNT(salary) from emp where salary>e.salary);

if U want nth lowest just put opreator "<" instead of putting ">".

 

if you want to find 3rd highest or nth highest then do the following query.. just try this n let me know..

select * from emp e
where (&n-1)=(select COUNT(salary) from emp where salary>e.salary);

if U want nth lowest just put opreator "<" instead of putting ">".

Regards..
Sudhagar.V

 

Hi.. its very simple.. here is the query ..

select name,salary from(select name,salary,row_number() over(order by salary desc) rowno from emp_table) where rowno=3;

or

To find nth highest salary the following query to help u.. same as above.little changes.. instead of put '3' put '&rowno'.....

select name,salary from(selectname,salary,row_number() over(order by salary desc) rowno from employee) where rowno=&rowno;

 

Hi.. the above query is wrong..

the correct query is

select name,salary from(select name,salary,row_number() over(order by salary desc) rowno from emp_table) where rowno=3;

if you want nth highest salary. then use '&rowno' in place of '3'. Ok..

 

did u notice that i have already posted the correct one after the wrong one...???

 

selectname,salary from(selectname,salary,row_number()over(orderby salary desc) rowno fromemp_table)where rowno=3;
this will not work correctly in the case where the salaries are repeating .....

 

select sal from(select sal,row_number() over(order by sal

desc) rowno from emp group by sal) where rowno= &<nth salary>


this i guess should be give correct results in all cases