Re: sql quiz

>create table foo ( x int );
>insert into foo values ( 1 );
>insert into foo values ( 4 );
>insert into foo values ( 7 );
>insert into foo values ( 8 );
>insert into foo values ( 9 );
>insert into foo values ( 10 );
>commit;
>
>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         |
+--------------------------------------------------------------+