Thread: Top-N vales for Group Functions

1. Registered User
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 ...

Suddha Satta Ray
India

2. Moderator.
Join Date
Sep 2002
Location
UK
Posts
5,171

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 ...

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
•