TurkBear wrote:
> 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...
>
>
>
>
A different way is to use something similar to
SELECT ..... FROM
(SELECT ROWNUM AS enum, .......
FROM TABLE_A WHERE ROWNUM <=100) a
WHERE a.enum = 100
rgds
/Svend Jensen
Received on Tue Jul 22 2003 - 14:57:07 CDT
