Results 1 to 2 of 2

Thread: Top 10 Results

  1. #1
    Join Date
    Sep 2003
    Location
    Greater London
    Posts
    17

    Unanswered: Top 10 Results

    Hello,

    How are you able to return the top 10 results of a query? I recall that 8i Intro to SQL covered this but I don't know the syntax.

    Kind regards,

    Gillian

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    You can use the RANK or DENSE_RANK functions:

    Code:
    SQL> select ename, sal from
      2  (
      3  select ename, sal, rank() over (order by sal desc) rnk
      4  from emp
      5  )
      6* where rnk <= 10;
    
    ENAME             SAL
    ---------- ----------
    KING             5000
    SCOTT            3000
    FORD             3000
    JONES            2975
    BLAKE            2850
    CLARK            2450
    ALLEN            1600
    TURNER           1500
    MILLER           1300
    WARD             1250
    MARTIN           1250
    
    11 rows selected.
    
    SQL> select ename, sal from
      2  (
      3  select ename, sal, dense_rank() over (order by sal desc) rnk
      4  from emp
      5  )
      6  where rnk <= 10;
    
    ENAME             SAL
    ---------- ----------
    KING             5000
    SCOTT            3000
    FORD             3000
    JONES            2975
    BLAKE            2850
    CLARK            2450
    ALLEN            1600
    TURNER           1500
    MILLER           1300
    WARD             1250
    MARTIN           1250
    ADAMS            1100
    
    12 rows selected.
    Or if you really want 10 records, even if there are ties as above, then use ROW_NUMBER instead:
    Code:
    SQL> select ename, sal from
      2  (
      3  select ename, sal, row_number() over (order by sal desc) rnk
      4  from emp
      5  )
      6  where rnk <= 10;
    
    ENAME             SAL
    ---------- ----------
    KING             5000
    SCOTT            3000
    FORD             3000
    JONES            2975
    BLAKE            2850
    CLARK            2450
    ALLEN            1600
    TURNER           1500
    MILLER           1300
    WARD             1250
    
    10 rows selected.

Posting Permissions

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