Go4Expert

Go4Expert (http://www.go4expert.com/)
-   Oracle (http://www.go4expert.com/forums/oracle-forum/)
-   -   SQL-Query to find third highest (http://www.go4expert.com/forums/sql-query-t21192/)

javapank 4Mar2010 08:03

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

techgeek.in 4Mar2010 15:30

Re: 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;


javapank 16Mar2010 19:29

Re: SQL-Query to find third highest
 
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 16Mar2010 22:01

Re: SQL-Query to find third highest
 
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;

javapank 23Mar2010 01:09

Re: SQL-Query to find third highest
 
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 6Apr2010 17:29

Re: SQL-Query to find third highest
 
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 6Apr2010 17:29

Re: SQL-Query to find third highest
 
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 23Apr2010 10:12

Re: SQL-Query to find third highest
 
Quote:

Originally Posted by javapank (Post 65093)
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 23Apr2010 10:18

Re: SQL-Query to find third highest
 
Quote:

Originally Posted by techgeek.in (Post 65111)
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..

techgeek.in 23Apr2010 23:17

Re: SQL-Query to find third highest
 
Quote:

Originally Posted by sudhagar (Post 67352)
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...???


All times are GMT +5.5. The time now is 16:26.