Re: ROWNUM question

> Can anybody tell me why? Is there something magical about rownum that
> you cannot use it in compound comparisons?

hi!

since rownum is a pseudo-column that counts _returned_ rows, it always starts at 1
you may apply whatever restrictions to your query ie. where name like '%Doe' the first row will always be returned with rownum = 1.

so if you make a query where rownum > 20000, what happens... (simplified)
for example: oracle does a scan on a table, gets first row, the rownum = 1 - so it won't be returned gets second, rownum will still equal to 1 because there is no rows returned previously - it won't be returned
gets third ... rownum = ?
and so on .

.
.
.

so the server process always compares if 1 > 20000 and that will be always false.

you should get this working by using a nested query.. but don't trust rownums, as you shouldn't trust rowids
unless you're sure that nobody writes to these tables.

hope this helps a little,
tanel. Received on Fri Oct 08 1999 - 07:55:11 CDT