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

# SQL-Query to find third highest

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;

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

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;

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

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

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

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;

Quote:

Originally Posted bytechgeek.inCode: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..

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