Re: something appears to be funny with ROWNUM

rownum is a bit misleading. its really just a counter of the rows returned in the resultset.

so lets say you go:

select *
from myTable
where rownum <= 10;

you get 10 records. Your 'rownum' counter starts with the records returned.

now do the following

select *
from myTable
where myCol like '%myvalue%'
and rownum < 10;

well your 'rownum' counter starts with the first 10 values that meet your like statement. Which is called
your 'resultset'.

so if you do

select *
from myTable
where rownum between 500 and 600;

your select is looking at the first row it will default to a rownum = 1; thats not 500 so it gets discarded
second row gets examined, it also gets a default rownum = 1(since no rows have been returned). that isnt 500, discard

and so on.

to get the the records 'between' a certain rownum, you gotta use a couple of inline views.

http://asktom.oracle.com/pls/ask/f?p=4950:8:404569119689273749::NO::F4950_P8 _DISPLAYID,F4950_P8_CRITERIA:127412348064, its generic. takes a bit to figure out why it works. now your in 8.0 right? I dont think you can do an order by in a sub-query... so this might not be possible...

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <[EMAIL PROTECTED]> Sent: Tuesday, July 08, 2003 10:14 PM

> 1. this first query gave me 600 lines of output.
>
> SELECT Rownum, Gender
> FROM EMP2
> WHERE ROWNUM <= 600;
>
> ------------
>
> 2. this one right here told me no rows selected...
>
> SELECT Rownum, Gender
> FROM EMP2
> WHERE ROWNUM BETWEEN 500 and 600;
>
> I dont get it... something is funny with ROWNUM...
>
>
> Any ideas?
> thx
> maa
>
> __________________________________
> Do you Yahoo!?
> SBC Yahoo! DSL - Now only $29.95 per month!
> http://sbc.yahoo.com
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.net
> --
> Author: MaryAnn Atkinson
> INET: [EMAIL PROTECTED]
>
> Fat City Network Services -- 858-538-5051 http://www.fatcity.com
> San Diego, California -- Mailing list and web hosting services
> ---------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Ryan
  INET: [EMAIL PROTECTED]

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).