Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Limiting the resultset returned from a SELECT to the first N rows?

Re: Limiting the resultset returned from a SELECT to the first N rows?

From: Jurij Modic <jurij.modic_at_mf.sigov.mail.si>
Date: 1997/10/28
Message-ID: <34564605.2889823@www.sigov.si>#1/1

On Tue, 28 Oct 1997 11:49:06 +0100, "H. Fellinghauer" <hfr-NOSPAM-_at_metzler.com> wrote:

>Michael G. Schneider wrote:
>> I would like to write a SELECT statement and limit the number of returned
>> rows. Something like
>>
>> SELECT * FROM tbl ORDER BY amount TOP 10
>>
>> Is this possible?
>
>Try the following:
> SELECT * FROM tbl WHERE rownum < 10 ORDER BY amount;
>

This won't returned the wanted result because WHERE condition is applied before ORDER BY clause. However, there are a couple of ways to do this.

The easiest way (and most simple) is a PL/SQL block with an ORDER BY in a cursor and a counter variable to stop fetching rows after first N rows.

But sometimes we have to stick with pure SQL because there are some circumstances where PL/SQL is not desirable or appropriate. Here are some posibble BOTTOM_N/TOP_N solutions in Oracle's SQL - because of simplicity the well known table SCOTT.EMP is used and the task is to select 10 employes with lowest/highest salary:

  1. Query with subselect - it mimics what most of us are doing when we must select 10 bottom/top records from a list of records on a paper and we dont have a computer ;-)

Bottom N:
  SELECT * FROM scott.emp e1
    WHERE 10 >=

   	(SELECT COUNT(sal)
		FROM scott.emp e2
		WHERE e2.sal <= e1.sal)

    ORDER BY e1.sal;

Top N:
  SELECT * FROM scott.emp e1
    WHERE 10 >=

  	(SELECT COUNT(sal)
		FROM scott.emp e2
		WHERE e2.sal >= e1.sal)

    ORDER BY e1.sal DESC;

This solution is easy to understand but is terrible slow when number of reccords is not very small - use it on a tables with no more then a couple of hundreds of records.

2. Use of an inline view - the view is used to perform implicit sorting because of GROUP BY, which in this case is applied before WHERE condition

Bottom N:
  SELECT emp.* FROM emp,
    (SELECT rowid x, sal FROM emp
     GROUP BY sal, rowid) e2
  WHERE emp.rowid = e2.x AND rownum <= 10   ORDER BY emp.sal;

Top N: (the descending sort order is forced by "-sal")   SELECT emp.* FROM emp,
    (SELECT rowid x, -sal FROM emp
     GROUP BY -sal, rowid) e2
  WHERE emp.rowid = e2.x AND rownum <= 10   ORDER BY emp.sal DESC;

The performance of this type of solution is quite good (specialy if there is no index on driving column - in our example SAL), but still considerably slower then PL/SQL solution.

Inline views are not available prior to Oracle 7.2 - in that case you must create view explicitely, which may not be very practical option.

3. "Dummy" outer join with very small table - again, "useless" outer join is used to force implicit sorting before the ROWNUM condition.

Bottom N:
  SELECT emp.* FROM emp, dual
  WHERE emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL)     AND rownum <= 10
  ORDER BY emp.sal;

Top N: (again, the descending sort order is forced by "-sal")   SELECT emp.* FROM emp, dual
  WHERE -emp.sal = DECODE(dual.dummy(+),'X',NULL,NULL)     AND rownum <= 10
  ORDER BY emp.sal DESC;

The DECODE function is used to assure that the select from DUAL will return NULL so that no match will be found with the select from EMP. Instead of SYS.DUAL you can use any small table (but DUAL is usualy the smallest and most appropriate for this purpose).

The performance of this solution is unexpectedly brilliant - in fact the speed is the same as with PL/SQL solution!

I did some testing with a table of 100.000 records with and without the index on driving column. The response times with solution 3. were exactly the same as with PL/SQL while the solution 2. was a little slower. The solution 1. was unusable with this amount of records.

>HTH.
> Harry

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 Oct 28 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US