Re: How to get the n-th row?

In article <34BD61D5.95233B0F_at_www.ecsinc.net>,

   Daniel Askey <daniel_at_www.ecsinc.net> wrote:
>
>
>Quick answer: select * from table_name where rownum=10;
>But the result will change as the info in the database change.
>(refer to Peter Schneider 's reply)

Oops, unfortunately this won't return any records at all. Rownum is assigned to a row while oracle is building the result set. The first row Oracle retrieves (rownum=1) will be checked against the where-clause. Since rownum<>10, the check will fail. The second row will also get rownum=1 because it is the first row in the result set, so also this row will fail the where clause. And so on..

What will work is:
select * from table_name where rownum<11 minus
select * from table_name where rownum<10

I don't think this is a good solution, since it will be incredibly slow for large tables.
The best solution for identifying a record is to use it's primary key, this will allways work. Second best is using rowid, but this will fail if a record is deleted and restored to the table.

Stefan.

--
Name      :G.R.S. Deisz
Phone     :+31-50-5855954
E mail    :G.R.S.Deisz_at_PTT-Telecom.Unisource.NL
DISCLAIMER:This statement is not an official statement from, nor
           does it represent an official position of, PTT Telecom BV.