[javapank]

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

[techgeek.in]

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;

[javapank]

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

[techgeek.in]

Try this..itz working fine..

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;

[javapank]

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

[sudhagar]

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 ">".

[sudhagar]

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

[sudhagar]

Quote:

Originally Posted by javapank

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

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;

[sudhagar]

Quote:

Originally Posted by techgeek.in

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.. the above query is wrong..

the correct query is

selectname,salary from(selectname,salary,row_number()over(orderby salary desc) rowno fromemp_table)where rowno=3;

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

[techgeek.in]

Quote:

Originally Posted by sudhagar

Hi.. the above query is wrong..

the correct query is

selectname,salary from(selectname,salary,row_number()over(orderby salary desc) rowno fromemp_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...???