Re: to sqlplus expert. - is this sqlplus version problem?

From: William Robertson <william.robertson_at_bigfoot.com>
Date: 14 Oct 2005 07:55:42 -0700
Message-ID: <1129301742.295527.130720@g44g2000cwa.googlegroups.com>

jdyang wrote:
> Hello. I'm newbie of sqlplus.
>
> I use sql plus 8.1.5.0.0.
>
> I want to get row between 3 to 5. like,
>
> select * from table where rownum >= 3 and rownum =< 5;
>
> and when I use above query, there is no record.
>
> I found reason from google that rownum is attached to row when that row
> pass the where condition.
> And from another webpage, I found the message that those query are
> working well.
>
> how I can get row between 3 to 5. Above query is just version problem?
>
> Infact, I'm using temporary query:
> select * from
> ( select <column names>, rownum as temp_rownum where <table> )
> where temp_rownum >= 3 and temp_rownum <=5;
>
>
> and surely, I'm not satisfied.
>
> Can you help me please? - to sql experts.

Try something like:

SELECT object_id
FROM ( SELECT object_id

              , ROW_NUMBER() OVER (ORDER BY object_id) AS rn
         FROM   user_objects )

WHERE rn BETWEEN 3 AND 5;

btw this is about SQL (querying language) not SQL*Plus (application). Received on Fri Oct 14 2005 - 09:55:42 CDT