DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 13

Thread: TOP FIVE

  1. #1
    Join Date
    Jun 2001
    Posts
    56

    How I get top Five salaries ?

    Salaries
    ----------
    2500
    5300
    5400
    6750
    7500
    3500
    2500
    6500
    5600


  2. #2
    Join Date
    Jul 2001
    Posts
    334
    Try this

    select salaries from emp
    where rownum < 6
    order by salaries desc;

  3. #3
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    to aph: your query gets first five rows from emp and orders them by salary (rows get their rownums BEFORE ordering):


    SQL> select salaries from emp;

    SALARIES
    ----------
    2500
    5300
    5400
    6750
    7500
    3500
    2500
    6500
    5600

    9 rows selected.

    SQL> select salaries from emp
    2 where rownum < 6
    3 order by salaries desc;

    SALARIES
    ----------
    7500
    6750
    5400
    5300
    2500




    to mahmood:

    select salaries from
    (select salaries from emp order by salaries desc)
    where rownum<6

    Ales



  4. #4
    Join Date
    Jun 2001
    Posts
    56

    Dear Friends
    When u reply, First of all check ur ans: ur self ?
    Thanx

  5. #5
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    of course, I checked it twice ... something is wrong?

  6. #6
    Join Date
    Mar 2001
    Posts
    77
    Ales, what happens if there were two salaries belong to the top five list which were exactly the same? Such as:

    5300
    5300
    5400
    6750
    4500
    3500
    2500
    2300
    2600


  7. #7
    Join Date
    Jul 2001
    Posts
    334
    Oraka6
    Well, in case of same both salaries, The 2nd salary based on your question Will also consider as a top. There is no problem.


  8. #8
    Join Date
    Jul 2000
    Posts
    296
    There is a problem if you use rownum and salaries are exactly the same.

    In 8.1.6 (an d8.1.7 and 9) you can use:

    SELECT ename, sal
    FROM
    ( SELECT ename, sal, RANK() OVER (ORDER BY sal DESC) as rank
    FROM emp
    )
    WHERE rank <= 5;

    In 7, 8 and 8.1.5 you can use:
    SELECT e1.ename, e1.sal
    FROM emp e1
    WHERE 5 > (SELECT COUNT(1) FROM emp e2 WHERE e2.sal > e1.sal)
    ORDER BY sal DESC;

  9. #9
    Join Date
    Apr 2001
    Location
    Czechia
    Posts
    712
    oraka6, if there were two exactly same salaries that belong to the top five, we would get them both in the result:
    SALARIES
    ----------
    6750
    5400
    5300
    5300
    4500

    If we don't want to get them both, we have to use DISTINCT in the inner query.
    Both the queries (with or without DISTINCT) could answer the original question since it is a bit vague.

    akkerend, what's the problem if we use rownum and salaries are the same? Could you expand on that, please?
    Thank you.
    Ales


  10. #10
    Join Date
    Jul 2001
    Posts
    334
    Yes Oraka6 I am 100% agree with Ales, Almost same I have also explained to you before.

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