Re: selecting specified rows from a view using rownum

From: Yuri Korostyshevskiy <ykoros_at_dowjones.net>
Date: 1997/12/01
Message-ID: <34830BF4.94B7A573@dowjones.net>#1/1

Try using minus instead of intersect. Here is a quick example:

SQL> desc b

 Name                            Null?    Type
 ------------------------------- -------- ----
 BCOL                                     VARCHAR2(10)

SQL> select * from b;

BCOL

---

a
b
c
d
e
f

6 rows selected.

SQL> select * from b where rownum < 6
  2 minus
  3 select * from b where rownum < 3;

BCOL

---

c
d
e

SQL> spool off

Hope this helps

Yuri Korostyshevskiy

Frank Curti wrote:

> I am working on a web based application using Netscapes Livewire and Oracle
> 7.3. I create a view based on a query and I need to be able to get 25 rows
> at a time. When I make the following query it does not work.
>
> select * from view1 where rownum <= 50 intersect select *
> from view1 where rownum >= 25;
>
> The Oracle documentation is pretty clear why it does not work(it says the
> second select statement assigns a rownum value equal to 1 which makes the
> rownum >= 25 statement false) but does not tell me how to work around the
> problem. Can I some way still use rownum or is there a way I can insert
> another column into the view?
>
> Thanks in advance,
> Frank Curti
Received on Mon Dec 01 1997 - 00:00:00 CST