SQL-Query to find third highest

javapank's Avatar, Join Date: Mar 2010
Newbie Member
How to find third highest salary from emp table in Oracle.?
0
techgeek.in's Avatar, Join Date: Dec 2009
Skilled contributor
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;
0
javapank's Avatar, Join Date: Mar 2010
Newbie Member
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
0
techgeek.in's Avatar, Join Date: Dec 2009
Skilled contributor
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;
0
javapank's Avatar, Join Date: Mar 2010
Newbie Member
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
0
sudhagar's Avatar, Join Date: Apr 2010
Light Poster
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 ">".
0
sudhagar's Avatar, Join Date: Apr 2010
Light Poster
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
0
sudhagar's Avatar, Join Date: Apr 2010
Light Poster
Quote:
Originally Posted by javapank View Post
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;
0
sudhagar's Avatar, Join Date: Apr 2010
Light Poster
Quote:
Originally Posted by techgeek.in View Post
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.. 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..
0
techgeek.in's Avatar, Join Date: Dec 2009
Skilled contributor
Quote:
Originally Posted by sudhagar View Post
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...???