Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: rownum question

Re: rownum question

From: Mark Rosenbaum <mjr_at_netcom.com>
Date: 1997/01/30
Message-ID: <mjrE4sqE7.sq@netcom.com>#1/1

In article <32EFD1DB.A7D_at_sequeltech.com>, <ptsao_at_sequeltech.com> wrote:
>Hi,
>
>I was able to do
>
> select * from table where rownum = 1;
> select * from table where rownum <= 20;
> update table
> set id = rownum;
>
>but, I CAN NOT do
>
> select * from table where rownum = 2;
> select * from table where rownum = 3;
> ...
>
>Can someone tell me why?

Yes,

rownum is the number of the row from the return set if the row fails the where clause then rownum is never incremented.

For a general solution to this problem create a view.

Something like

create view derf as
select table.*, rownum rownumber from table;

Then select from derf instead of the table.

BTW the above SQL was not tested but, it is a start.

Hope this helps

Mark Rosenbaum			Otey-Rosenbaum & Frazier, Inc.
mjr_at_netcom.com			Consultants in High Performance and
(303) 727-7956			Scalable Computing and Applications
POB 1397			ftp://ftp.netcom.com/pub/mj/mjr/resume/
Boulder CO 80306 Received on Thu Jan 30 1997 - 00:00:00 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US