SQL-Query to find third highest

Discussion in 'Oracle' started by javapank, Mar 4, 2010.

  1. javapank

    javapank New Member

    Joined:
    Mar 4, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    How to find third highest salary from emp table in Oracle.?
     
  2. techgeek.in

    techgeek.in New Member

    Joined:
    Dec 20, 2009
    Messages:
    572
    Likes Received:
    19
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    http://www.techgeek.in
    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;
    
     
  3. javapank

    javapank New Member

    Joined:
    Mar 4, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  4. techgeek.in

    techgeek.in New Member

    Joined:
    Dec 20, 2009
    Messages:
    572
    Likes Received:
    19
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    http://www.techgeek.in
    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;
    
     
  5. javapank

    javapank New Member

    Joined:
    Mar 4, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     
  6. sudhagar

    sudhagar New Member

    Joined:
    Apr 6, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Chennai
    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 ">".
     
  7. sudhagar

    sudhagar New Member

    Joined:
    Apr 6, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Chennai
    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
     
  8. sudhagar

    sudhagar New Member

    Joined:
    Apr 6, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Chennai
    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;
     
  9. sudhagar

    sudhagar New Member

    Joined:
    Apr 6, 2010
    Messages:
    6
    Likes Received:
    0
    Trophy Points:
    0
    Occupation:
    Software Engineer
    Location:
    Chennai

    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..
     
  10. techgeek.in

    techgeek.in New Member

    Joined:
    Dec 20, 2009
    Messages:
    572
    Likes Received:
    19
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    http://www.techgeek.in
    did u notice that i have already posted the correct one after the wrong one...???
     
  11. PRASOON

    PRASOON New Member

    Joined:
    Jun 4, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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 .....
     
  12. PRASOON

    PRASOON New Member

    Joined:
    Jun 4, 2010
    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    0
    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
     

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice