Results 1 to 2 of 2
  1. #1
    Join Date
    Dec 2002
    Posts
    5

    Unanswered: Top-N vales for Group Functions

    Hi ...

    How do I get Top-N values for columns that are a result of applying aggregate functions like SUM, AVG on database table columns.
    Like Top-10 "Average" Sales or Lowest 50 outstanding amounts for current Fin. Year .. like these ...

    I hope my question is clear ...

    Thanks in advance ..

    Suddha Satta Ray
    India

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: Top-N vales for Group Functions

    Originally posted by suddhasatta
    Hi ...

    How do I get Top-N values for columns that are a result of applying aggregate functions like SUM, AVG on database table columns.
    Like Top-10 "Average" Sales or Lowest 50 outstanding amounts for current Fin. Year .. like these ...

    I hope my question is clear ...

    Thanks in advance ..

    Suddha Satta Ray
    India
    There are a couple of ways of doing this. To decide which is best for you, you need to consider what exactly you mean by top 10. For example, what if 2 rows "tie" for first place - do you just want 8 more records, or 9 more?

    One way (using Oracle emp table):

    SELECT deptno, avg_sal
    FROM
    ( SELECT deptno, AVG(sal) avg_sal
    GROUP BY deptno
    ORDER BY AVG(sal) DESC
    )
    WHERE ROWNUM <= 10;

    Or using analytic functions:

    SELECT deptno, avg_sal
    FROM
    (
    SELECT deptno, avg_sal, RANK() OVER (ORDER BY sal DESC) rank
    FROM
    (
    SELECT deptno, AVG(sal) avg_sal
    FROM emp
    GROUP BY deptno
    )
    )
    WHERE rank <= 10;

    Or same again, but using DENSE_RANK() instead of RANK()

    This query demonstrates the different results for the different methods:

    select empno, sal, rank, dense_rank, ROWNUM from
    (
    select empno, sal, rank() over (order by sal desc) rank, dense_rank() over (order by sal desc)
    from emp
    order by sal desc
    );

    EMPNO SAL RANK DENSE_RANK ROWNUM
    ---------- ---------- ---------- ---------- ----------
    7839 5000 1 1 1
    7788 3000 2 2 2
    7902 3000 2 2 3
    7566 2975 4 3 4
    7698 2850 5 4 5
    7782 2450 6 5 6
    7499 1600 7 6 7
    7844 1500 8 7 8
    7934 1300 9 8 9
    7521 1250 10 9 10
    7654 1250 10 9 11
    7876 1100 12 10 12
    7900 950 13 11 13
    7369 800 14 12 14

    i.e. the ROWNUM method gives the first 10 records, the RANK method gives the first 11 records, and the DENSE_RANK method gives the first 12 records.

Posting Permissions

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