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/4385