Re: Tricky SQL Problem: Top "n" of queried records

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1998/03/17
Message-ID: <350e97c5.30061357@www.sigov.si>#1/1

On Tue, 17 Mar 1998 03:20:40 -0600, rtgraf_at_sintec.de wrote:

>How can I restrict the result of a SQL query to, say, the top 10 regarding
>some defined order criteria, e.g.
>
>SELECT deptno, sum(salary) FROM emp
>GROUP BY deptno
><ONLY the top 3 w/regards to sum(salary)>;
>
>I do *not* want the client application to fetch only the first three records,
>I want the Server to retrieve exactly these rows.
>
>I have a rather quirky solution using a multiply nested subquery (one nesting
>level per "n" in the topic!)
>
>The problem is in fact a general one and I seem to have read a solution
>somewhere but forgot the source ;-(

Here is a sollution that works for Oracle 7.2 and upper. In my example I'm looking for top 3 sallaries summed by JOB, not by DEPTO, because there only 3 different departments in original SCOT.EMP table, while there is 5 different JOBS in it.

First, let us see the summed sallary for all jobs:

SQL> SELECT job, SUM(sal) sal FROM emp GROUP BY job;

JOB SAL
--------- ---------

ANALYST        6000
CLERK          4150
MANAGER        8275
PRESIDENT      5000
SALESMAN       5600

Now, here is a query that will return top 3 summed sallaries:

SQL> SELECT a.job, a.sal FROM

  2     (SELECT job, SUM(sal) sal FROM emp GROUP BY job) a,
  3     dual

  4 WHERE -1*sal = DECODE(dual.dummy(+),'X',0,0)   5 AND ROWNUM <= 3
  6 ORDER BY a.sal DESC;

JOB SAL
--------- ---------

MANAGER        8275
ANALYST        6000
SALESMAN       5600

This query is quite efficient also for larger tables. I tested its logic on a 100.000 records table and its performance was only 5-10% worse than ordinary PL/SQL sollution.

Now some remarks about the query itself. Since ROWNUM condition is allways applied befor ORDER BY operation, the sorting on summed sallary must be done implicitely. This is achived with outer join (line 4) with dummy table. -1*sal in an outer join expression forces the implicit sorting to be done in descending order. The in-line view (line 2) is used, because you can not use group function (SUM(sal)) in an outer join. The ORDER BY in the last line is used only to sort the resulting 3 rows. Note that this query will allways return 3 rows (as long as there is at least 3 different jobs in a table), even if there are more jobs with equal sum of sallaries on the 3rd possition.

Regards,

---

Jurij Modic                             Republic of Slovenia
jurij.modic_at_mf.sigov.mail.si		Ministry of Finance
============================================================

The above opinions are mine and do not represent any official standpoints of my employer Received on Tue Mar 17 1998 - 00:00:00 CST