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

Thread: Top N Analysis

  1. #1
    Join Date
    Apr 2003
    Posts
    29

    Top N Analysis

    i want to find out top 5 salaries of employees.
    I am working with Oracle 8.0.5. This query seems not to work. Can u give me another solution to this query. I dont want to achieve the result through a cursor.

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

  2. #2
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Re: Top N Analysis

    Originally posted by varshanswamy
    This query seems not to work.
    How? Why? Error message? Wrong answer?

  3. #3
    Join Date
    Apr 2003
    Posts
    29
    the erroe i get is ERROR at line 1:
    ORA-00907: missing right parenthesis

  4. #4
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    1 select empno,sal
    2 from emp e
    3 where 5>(select count(*) from emp
    4 where sal>e.sal)
    5* order by sal desc
    SQL> /

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

    SQL> set pagesize 100
    SQL> select empno,sal from emp
    2 order by sal desc
    3
    SQL> /

    EMPNO SAL
    ---------- ----------
    7839 5000
    7788 3000
    7902 3000
    7566 2975
    7698 2850
    7782 2450
    7499 1600
    7844 1500
    7934 1300
    7521 1250
    7654 1250
    7876 1100
    7900 950
    7369 800

    14 rows selected.

  5. #5
    Join Date
    Apr 2003
    Posts
    29
    thanx a lot!!!

  6. #6
    Join Date
    Apr 2003
    Posts
    29
    Hi hrishy

    Can u explain the logic for the query esp the inner query.

    bye.
    Aparajita.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Btw, the suggested method is the slowest of all possible pre-8i top-N query variations.
    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
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    Originally posted by varshanswamy
    the erroe i get is ERROR at line 1:
    ORA-00907: missing right parenthesis
    "order by" is not permitted in in-line views in 8.0.5, I believe.
    David Aldridge,
    "The Oracle Sponge"

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

    Oracle ACE

  9. #9
    Join Date
    Jan 2001
    Posts
    2,828
    Originally posted by varshanswamy
    Hi hrishy

    Can u explain the logic for the query esp the inner query.

    bye.
    Aparajita.

    Hi Aparajita

    The inner query is called co-releated sql query in Relation Database Terminology.A co-releated sql query executes once for the row returned by the main query.Look up the documentation or search google for co-releated subquery.

    A ordinary subquery on the other hand is run once only

    Hope the explanation was useful.

    In 8.0.5 since analytical functions are not avaliable the above query is the way to go.

    regards
    Hrishy

  10. #10
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by hrishy
    In 8.0.5 since analytical functions are not avaliable the above query is the way to go.
    As I already said, this is the slowest of all possible top-n solutions for 8.0.5. Also you might be surprised that sometimes your query will not return 5 records (for top-5 example of yours), it might return you anything between 0 and 5 records. If for example on emp table you wanted two highest salaries, you would only get one with your query, i.e.

    EMPNO SAL
    ---------- ----------
    7839 5000

    If you want to check some more efficient top-N possibilities in pre-8i, you might check this thread:
    http://www.dbasupport.com/forums/sho...threadid=10310
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

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