Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> Re: How to select a range of records by rownum?
Pao Wan wrote:
>
> I want to select records from 40 to 100, and I think the statement
>
> "select rownum, user, email from clients where rownum >= 40 and rownum <=100"
>
> will work. But it failed, I tried
>
> "select rownum, user, email from clients where rownum <= 100"
>
> it work. But
>
> "select rownum, user, email from clients where rownum >= 40" failed, can
> anybody help me.
>
It will never work.
ROWNUM is evaluated AFTER the query and BEFORE and ORDER BY.
It's just a sequential number assigned on the fly to the rows that are
a
result of the query. You can rely on that.
Oracle alows you to select on ROWNUM but just for the first "n" rows.
It may be good, it may be bad but it's like that.
You have to rely on your own data (i.e. part of a table) to get a range
of
rows:
SELECT .... WHERE myOrder BETWEEN lowerBound AND upperBound
Hope this helps
Renato
-- ______________________________________________________________________________ Renato Brazioli Sodalia SpA e-mail: brazioli_at_sodalia.it Via Valentina Zambra,1 voice: +39(461)316.154 (direct) I-38100 Trento fax: +39(461)316.401 ITALY http://www.geocities.com/Paris/4385Received on Mon Mar 24 1997 - 00:00:00 CST