Sql
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: Sql

  1. #1
    Join Date
    Feb 2003
    Location
    Mumbai, India
    Posts
    10

    Sql

    Hi,
    I'll be happy if anybody can tell me a query which retrieves the second highest salary and the top five salaries from EMP table.

    Tx

    Kiran

  2. #2
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    Second highest:
    Code:
    select min(sal) from (select distinct sal from emp order by sal desc) where rownum<=2;
    Top five:
    Code:
    select sal from (select distinct sal from emp order by sal desc) where rownum<=5;
    Ales
    The whole difference between a little boy and an adult man is the price of toys

  3. #3
    Join Date
    May 2001
    Location
    Dallas, US
    Posts
    78

    Use Rank function

    Hi,
    Use Rank function..

    SELECT Empno, Ename, Job, Mgr, Sal,
    RANK() OVER
    (ORDER BY SAL Desc NULLS LAST) AS Rank
    FROM Emp
    ORDER BY SAL Desc NULLS LAST;

    Hope this will help u.

    Regards
    RP Kumar
    You Can Win, if u believe Yourself

  4. #4
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    SELECT empno, sal
    FROM (SELECT empno, sal
    FROM emp
    ORDER BY sal DESC)
    WHERE ROWNUM <=n (if n=5)

    EMPNO SAL
    ---------- ----------
    7839 5000
    7788 3000
    7902 3000
    7566 2975
    7698 2850

    if n=5 gives the Top 5 salaries just change the Rownum n u get Top n Values

    Thanks
    Sridhar

  5. #5
    Join Date
    Feb 2003
    Location
    Mumbai, India
    Posts
    10
    Hi,
    many tx for the queries. but an error is being generated, at the 'order by' clause.
    will be happy enough for the right query.

    Kiran

  6. #6
    Join Date
    Dec 2002
    Location
    India
    Posts
    34
    it should not throw any error in the Order by clause!! I have tested the query again..

    Thanks
    Sridhar

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width