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

Thread: Order values

  1. #1
    Join Date
    Apr 2001
    Posts
    17

    Question

    Hi,

    How can I retrieve specified number of highest value columns with a single query;

  2. #2
    Join Date
    Aug 2000
    Posts
    462
    Select the 5 highest empno's from scott.emp:

    select * from scott.emp where rownum < 6 order by empno desc;


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Well, one has to be fair and explain that the kmesser's sollution is more an exception then a general sollution. If instead of EMPNO you put SAL column yo won't get the 5 employees with highest sallaries! The more general solution for top-N queries would be:

    select * from (select * from emp order by sal desc) where rownum < 6;

    This will return 5 top paid employees. It is also worth noting that the above query will work only in 8i, because you can't use ORDER BY inside a view prior to 8i. Kmesser's query will work also with earlier releases, but the results will be wrong with pre-8i databases, because the optimizer's operation STOPKEY, which causes the results to be correct, was introduced in 8i.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Aug 2000
    Posts
    462
    jmodic:

    Wow! Thanks for all that additional info. I simply opened a SQL*PLUS session, tested my initial idea, and it worked.

    This just goes to show you how complex the most apppropriate solution can be, even if other solutions seem to work perfectly.

    Thanks!

    I still don't understand why it worked with empno, but does not work with salary?! . . . Can you explain that for me?



    [Edited by kmesser on 05-06-2001 at 09:30 PM]

  5. #5
    Join Date
    Apr 2001
    Posts
    5
    hi friend,

    the query worked for empno because it is ur primary key column and has index on it. while rows are fetched by primary key column it will be fetched in ordered manner. so the query worked.
    for sal column first rows are fetched as in the database and only first 5 rows are fetched using primary key and with the result set it will order so u are not able to get the right answer.
    so order by works after rows are fetched from the database.

    bye.

    [Edited by dhani_in on 05-07-2001 at 12:19 AM]

  6. #6
    Join Date
    Apr 2001
    Posts
    17
    Thanks,
    But How I can do it in oracle 7 ?

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    In Orcle7 the top-N and bottom-N queries are much more complicated (particularry the top-N, where you have to sort records in descending order) and much less efficient.

    If we stick with the "top 5 paid employees" example from SCOTT.EMP, here are three different queries that will return correct results. The first one is the most "straightforward", but also by far the most inefficient for large tables. The last one is the fastest one, particulary if there is an index on the sorting column. Note also that if there are equal values at the end of the result set (e.g. if there are more then one employee with the same salary at the fifth position) the first query will return none of them, while other two will return only one of them.

    1. With corelated subquery
    SELECT * FROM scott.emp e1 WHERE 5 >=
    (SELECT COUNT(sal)
    FROM scott.emp e2
    WHERE e2.sal >= e1.sal)
    ORDER BY e1.sal DESC;

    2. With inline view
    SELECT emp.* FROM emp,
    (SELECT rowid x, -1*sal FROM emp GROUP BY -1*sal, rowid) e2
    WHERE emp.rowid = e2.x AND rownum <= 5
    ORDER BY emp.sal DESC;

    3. With "dummy" outer join
    SELECT emp.* FROM emp, dual
    WHERE -1*emp.sal = DECODE(dual.dummy(+), 'X', NULL, NULL)
    AND rownum <= 10
    ORDER BY emp.sal DESC;

    HTH,
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Apr 2001
    Posts
    17
    I need the employees who have the highest 3 values in the salary field
    , even there more then one has the same value.
    Ex.

    Empno Sal
    ----- ---
    1 1000
    2 500
    3 1000
    4 800
    5 700
    6 250
    7 800
    8 100

    The result has to be like :

    Empno
    -----
    1
    3
    4
    5
    7

  9. #9
    Join Date
    May 2001
    Location
    Asia
    Posts
    1

    Arrow duplicate


    hi guys
    how to get duplicate records from a table?

  10. #10
    Join Date
    Jul 2000
    Location
    india
    Posts
    213
    Hi rojy

    pls search the forumn somebody has already given the solution for the duplicate records...

    pras

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