Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: sql quiz
On Sat, 10 Jan 1998 11:49:02 GMT, Cezariusz.Marek_at_comarch.pl (Cezariusz Marek) wrote:
[snip]
>>
>>select rnum
>> from ( select rownum rnum
>> from all_objects
>> where rownum <= ( select max(x) from foo )
>> ), foo
>> where rnum = x (+)
>> and x is null
>>/
>>
>>We use the inline view "select rownu rnum ... " to generate a set of numbers
>>1..max(x). We then outer join the original set of numbers in foo to this set,
>>only keeping the rows from this join such that foo didn't have a value.
>>
>>that way, if the number of rows/max value in foo changed, the query would still
>>work. You just need to make sure that the table you use in the inline view
>>(all_objects in the above example) has at least as many rows in it as the max
>>value of X......
> >And what if I don't have a table with enough number of rows? Is there >a select, that will return _unlimited_ number of rows (which I could >safely limit by WHERE ROWNUM < xxx)? >
>And BTW -> how works WHERE ROWNUM < 5, if a table has for example >100.000.000 rows? Will the query stop after selecting 4 rows, or will >it count row numbers up to 100.000.000? >
>In other words, if exists select thet returns unlimited number of rows >thes is it possible to limit them by ROWNUM, or such the query would >never end? > >Regards, > >+--------------------------------------------------------------+ >| Cezariusz Marek Cezariusz.Marek_at_ComArch.pl | >| ComArch Bielsko-Bia³a tel. +48 33 143526 | >+--------------------------------------------------------------+
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD
http://govt.us.oracle.com/ -- downloadable utilities
Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Sat Jan 10 1998 - 00:00:00 CST