Re: Use of ROWNUM?

On 22 Jul 2003 10:11:33 -0700, ramon_at_conexus.net (Ramon F Herrera) wrote:

>I have bee using the ROWNUM constraint for a long time
>in its inequality form:
>
> SELECT somefield FROM mytable WHERE ROWNUM <= 100;
>
>This type of SELECT is very useful at development time,
>to reduce the amount of data returned. However, I was
>wondering whether I can use the ROWNUM in order to retrieve
>one specific record (suppose your data is kind of uninteresting
>in the first rows and you would like to take a look at, say
>record 100). It would be logical to use a statement like this:
>
> SELECT somefield FROM mytable WHERE ROWNUM = 100;
>
>But the equality form above only seems to work for ROWNUM=1.
>
>What I am looking for is something equivalent to awk's NR:
>
> awk 'NR==100 {print $0}'
>
>Thanks for your comments...
>
>-Ramon F. Herrera

You are using a RDBMS, aren't you? In an RDBMS you have data stored in what conceptually are sets. Sets don't have any order.

Also please look up the definition of rownum. ROWNUM is assigned *AFTER* query results are determined, but *BEFORE* order by. Even if you could use ROWNUM=100, you would still need to fetch 100 records.

If you really want record 100, you need to go back to the olden days of flat file systems. Many vendors are misusing databases as a flat file system anyway, so it shouldn't be too big of a problem for you.

Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address Received on Tue Jul 22 2003 - 12:47:47 CDT