Re: Use of ROWNUM?

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

ROWNUM is assigned as the rows are being returned so no rownum = 100 in the database - In most good relational databases ( like Oracle) , the 100th row is a meaningless request, since there is no way to be sure what data in stored in that row..

If you want to do it anyway:

 select rnum,somefield
  from
 (select rownum rnum,somefield from mytable)  where rnum = 100;

will do it... Received on Tue Jul 22 2003 - 12:53:05 CDT