Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Limiting the resultset returned from a SELECT to the first N rows?
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:
Bottom N:
SELECT * FROM scott.emp e1
WHERE 10 >=
(SELECT COUNT(sal) FROM scott.emp e2 WHERE e2.sal <= e1.sal)
Top N:
SELECT * FROM scott.emp e1
WHERE 10 >=
(SELECT COUNT(sal) FROM scott.emp e2 WHERE e2.sal >= e1.sal)
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