Re: Question: Finding the nth row in a table

In article <65flvc$2nt_at_jalisco.optimum.net>, Tim Boemker <tjb839_at_optimum.com> wrote:
>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.

As others have stated first you need an order of rows before the nth row makes sense.

It used to be that you could create a view with rownum in it and then use the rownum column (I always alias it to numrow) in the select.

The problem was, as I recall, that you could not use order by in a view. To get around this you could do a group by on the column concatenated (||) with rownum. All of the other columns need a max or min type function with the group by but since each group is only a single column that should not be an issue.

The problem now is that group by now seems to work with a hash instead of a sort so group by no longer sorts the rows as a side effect.

Possible alternatives

Later versions of Oracle allow selects in from clauses this might work.

Create a PL/SQL function.

Hope this helps

mjr Received on Wed Nov 26 1997 - 00:00:00 CST