1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

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:
    17
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    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:
    17
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    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:
    17
    Trophy Points:
    0
    Occupation:
    EOC (exploitation of computers)..i m a Terminator.
    Location:
    Not an alien!! for sure
    Home Page:
    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