Re: Limiting Rows Returned in Query

mlanda_at_vnet.ibm.com wrote:

>In <3283E1D4.57BC_at_c-square.com.nojunk>, Alvin Sylvain <alvin_at_c-square.com.nojunk> writes:
>>This may be a FAQ ... but I haven't seen it yet, so, what the hey.
>>
>>I want to be able to limit the number of rows returned in a query
>>statement. I seem to recall something such as:
>>
>> select columna, columnb
>> from tablea
>> where 1=1
>> and columnc > value
>> and maxrows <= 100; <<<<<<<<<<<<<<<<
>>
>>However, I can not find anything like this in the documentation. (It's
>>nice to be able to scan the entire CD-ROM, however, you have to know
>>what you're looking for! It's like having a dictionary to help your
>>spelling: if you don't know how to spell the word, you can't find it!
>>Speaking of which, anyone know how to spell "Medeavil?" Ie, as in
>>"Middle Ages"?)
>>
>>We're using Oracle 7.3.2 on Solaris 2.5.
>>
>>As a reference, this would be done as follows in Sybase 4.9:
>>
>> set rowcount 100 <<<<<<<<<<<<<<<<
>> select columna, columnb
>> from tablea
>> where 1=1
>> and columnc > value
>> go
>>
>>Any help is appreciated. Please e-mail to:
>> alvin_at_c-square.com
>>The normal "reply-to" is supposed to be broken to discourage junk
>>e-mail.
>>
>>Thanks!
>>========================================================================

>Try:

> select columna, columnb
> from tablea
> where 1=1
> and columnc > value
> and ROWNUM <= 100;

Careful when using rownum with an ORDER BY clause. The rownum is assigned BEFORE any sorts are done. So your're only getting the first 100 rows prior to sorting. The list will return sorted of course, but unless the rows where physically stored in sorted order, you could have an incorrect result. Personally, I would never use rownum for other than testing long running queries or programs.

Russ Received on Sun Nov 10 1996 - 00:00:00 CET