highest 3 values
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: highest 3 values

  1. #1
    Join Date
    Dec 2000
    Posts
    255

    highest 3 values

    Hi

    I was searching for highest 3 values finding query on forum. All I could found was query using ORDER BY in the INNER SELECT query. However I am on Oracle 8.0.5 where it is not working.
    There is an error whenever I put ORDER BY inside INNER SELECT in FROM clause. Is there any other way to that.

    I would like to make global query which can be run on any table for a specified column.

    Amol

    PS The query below gives error in my Oracle

    select sal
    from (select sal from emp order by sal desc)
    where rownum <= 3;

    from (select sal from emp order by sal desc)
    *
    ERROR at line 2:
    ORA-00907: missing right parenthesis

  2. #2
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    order by in inline view does NOT work in Oracle 8

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Try any of the following three examples (ordered from slowest to fastest):
    Code:
    SELECT /*+ RULE*/ ename, sal FROM scott.emp e1
    WHERE 3 >= 
            (SELECT COUNT(sal)
             FROM scott.emp e2
             WHERE e2.sal >= e1.sal)
    ORDER BY e1.sal DESC;
    
    
    
    SELECT /*+ RULE*/ emp.ename, emp.sal
    FROM scott.emp,
         (SELECT rowid x, -1*sal FROM scott.emp
          GROUP BY  -1*sal, rowid
         ) e2
    WHERE emp.rowid = e2.x AND rownum <= 3
    ORDER BY emp.sal DESC;
    
    SELECT /*+ RULE*/ emp.ename, emp.sal FROM emp, dual
    WHERE -1*emp.sal =   
          DECODE(dual.dummy(+),'X',NULL,NULL)
    AND rownum <= 3
    ORDER BY emp.sal DESC
    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
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Try any of the following three examples (ordered from slowest to fastest):
    Code:
    SELECT /*+ RULE*/ ename, sal FROM scott.emp e1
    WHERE 3 >= 
            (SELECT COUNT(sal)
             FROM scott.emp e2
             WHERE e2.sal >= e1.sal)
    ORDER BY e1.sal DESC;
    
    
    
    SELECT /*+ RULE*/ emp.ename, emp.sal
    FROM scott.emp,
         (SELECT rowid x, -1*sal FROM scott.emp
          GROUP BY  -1*sal, rowid
         ) e2
    WHERE emp.rowid = e2.x AND rownum <= 3
    ORDER BY emp.sal DESC;
    
    SELECT /*+ RULE*/ emp.ename, emp.sal FROM emp, dual
    WHERE -1*emp.sal =   
          DECODE(dual.dummy(+),'X',NULL,NULL)
    AND rownum <= 3
    ORDER BY emp.sal DESC
    Jurij :

    I wonder if this/any would work if max value of SAL is spread/duplicated.

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by abhaysk
    Jurij :

    I wonder if this/any would work if max value of SAL is spread/duplicated.
    Shure, they wil ALL work, why shouldn't they? (Hovewer the results you get from them might not be exactly what you have inspected ).

    Anyhow, the original question is too general to be answered exactly - it must have been much more precise. We don't know exactly whar he wants. So even if it could be done with analytical functions, we still don't know which one of them to use: ROW_NUMBER(), RANK, DENSE_RANK ?

    Anyway, to answer your question about the above queries and the "competing" values on the third position, let's say that the salaries in the sescending order are:

    - EMP1: 10.000
    - EMP2: 8.000
    - EMP3: 5.000
    - EMP4: 5.000
    - EMP5: 5.000
    - EMP6: 3.000

    In this case the first query will list only EMP1 and EMP2, while the second and the third will list EMP1, EMP2 and *any ONE* of the remaining eployees that earn 5.000. So the second and the third query will alwways return three records, but in case of the equal salaries you dont know which one of those with the same rank will be displayed.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    With an index on the salary column, you can do ...

    Code:
    Select /*+ INDEX_DESC(e my_salary_index) */
       ename,
       sal
    From
       scott.emp e
    Where
       Rownum < 4;
    It's very fast indeed, but you tun the risk that the optimizer might start ignoring the index_desc hint.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    Originally posted by jmodic
    Shure, they wil ALL work, why shouldn't they? (Hovewer the results you get from them might not be exactly what you have inspected ).

    Anyhow, the original question is too general to be answered exactly -
    it must have been much more precise.
    Well ofcourse the Q isnt precise, but what i had feeling from this post was, he wanted ** TOP ** 3 salaries...

    Originally posted by jmodic
    Anyway, to answer your question about the above queries and the "competing" values on the third position, let's say that the salaries in the sescending order are:

    - EMP1: 10.000
    - EMP2: 8.000
    - EMP3: 5.000
    - EMP4: 5.000
    - EMP5: 5.000
    - EMP6: 3.000

    In this case the first query will list only EMP1 and EMP2, while the second and the third will list EMP1, EMP2 and *any ONE* of the remaining eployees that earn 5.000. So the second and the third query will alwways return three records, but in case of the equal salaries you dont know which one of those with the same rank will be displayed.
    Assume data is some thing like this

    - EMP1: 10.000
    - EMP2: 10.000
    - EMP3: 10.000
    - EMP4: 8.000
    - EMP5: 5.000
    - EMP6: 3.000

    Then ur query will return EMP1, 2, 3 with SAL=10...but according to subject & post of the poster, this is not he wanted...(i assume/guess)

    Abhay.
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  8. #8
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    SLIMDAVE,
    INDEX_DESC may not work correctly in Oracle 8. Even though it is a valid hint.
    I think Jonathan Lewis pointed out the bug 3 or 4 years ago.

    Tamil

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