Re: query to produce to n

From: Jurij Modic <jmodic_at_src.si>
Date: Sun, 21 Mar 1999 22:01:10 GMT
Message-ID: <36f56c1f.19081125@news.arnes.si>

On Sat, 20 Mar 1999 21:51:26 -0500, Lee <76754.3213_at_CompuServe.COM> wrote:

>What the best way to produce a query result of showing the top
>number of
>rows? For example, If I would sum sales by product and I then
>only return
>the top 5 products.

Although this kind of TOP N queries seem trivial in a set-oriented language like SQL, they are in fact not so tivial to write/understand neither are they very efficient in most cases. If you can use any procedural language on top of SQL (like PL/SQL for example), this would in most cases be the most efficient way to get top N records.

If you must stick only to (Oracle's) SQL, then it is also important which release of Oracle server you are using. If you are using 8i (aka 8.1) then the query for your particular example would be quite simple:

SELECT product, sum_sal
FROM (SELECT product, SUM(sal) sum_sal

      FROM my_table ORDER BY SUM(sal) DESC) WHERE rownum <= 5;

If you are using release 8.0 or lower then you can not use ORDER BY inside a view. In this case you can use the following:

SELECT product, sum_sal
FROM (SELECT product, SUM(sal) sum_sal

      FROM my_table GROUP BY product)
WHERE 5 >=
  (SELECT COUNT(*) FROM
     (SELECT SUM(sal) x_sal FROM my_table GROUP BY product)    WHERE x_sal >= sum_sal);

If your table is very large then this query will be very inefficient. In this case the following query will perform better. It uses a hint ("RULE") to assure the optimizer will execute ordering when performing GROUP BY operation:

SELECT /*+ RULE */ product, sum_sal
FROM (SELECT MAX(product) product, -1*x_sal sum_sal

      FROM (SELECT product, -1*SUM(sal) x_sal
            FROM my_table GROUP BY product)
      GROUP BY x_sal)

WHERE rownum <= 5;

>tia,
>Lee

HTH,
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Sun Mar 21 1999 - 16:01:10 CST