Re: Question: Finding the nth row in a table

In article <347a8aba.6540154_at_news.via.at> hferraz_at_via.at (Heber Ferraz-Leite) writes:

> select whatever from atable where rownum=n oder by whatever;
>
> You need the order by in this case, since you need to tell the
> database you want the nth according to this ordering. Otherwise you
> will receive a random order. (Not really random, but you can't
> control it)

According to "Oracle7 Server SQL Language Reference Manual" p. 2-43, "ORACLE assigns a ROWNUM value to each row as it is retrieved, before rows are sorted for an ORDER BY clause, so an ORDER BY clause normally does not affect the ROWNUM of each row." Furthermore, "rownum=n" will never be satisfied except when n=1 because the ROWNUM of the first row retrieved, by definition, is 1; conditions involving ROWNUM in SELECT statements should be of the form "ROWNUM < n". In short, the given SQL will not return any rows, and, if it did, they would be the wrong ones anyway. Received on Tue Nov 25 1997 - 00:00:00 CST