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

Home -> Community -> Usenet -> c.d.o.tools -> Re: How to use ROWNUM

Re: How to use ROWNUM

From: Michael J. Ort <michael_ort_at_my-deja.com>
Date: 2000/04/08
Message-ID: <8cnpic$qci$1@nnrp1.deja.com>#1/1

I feel compelled to reply to this since you used the word 'tuple' ;)

ROWNUM is assigned during execution. The first row is assigned the ROWNUM of 1. Since it does not meet your criterion, it is discarded. The next row is selected and assigned a ROWNUM of 1!!! Thus you can understand why it returns no rows...

There are many ways to get around this, but none of them are pretty. If you want a SQL way around it, you can use this, but it will load more and more rows into the buffer, the deeper your users browse into the data (most won't hit 'Next' 10000 times):

SELECT
  temp.*
FROM
  (
  SELECT
    ROWNUM numrow
  , table.*
  FROM
    table
  WHERE ROWNUM < 201
  ) temp
WHERE temp.numrow > 99;

Michael J. Ort

In article <8cmnnb$stp$1_at_news1.sinica.edu.tw>,   "Vincent Chen" <vincent_at_iuhua.com.tw> wrote:
> Dear all
>
> I have a very large table.
> And the content in this table will be display in Web page.
> (using ADO + ODBC)
>
> Since the size of table is too big, I wish to display all tuples
 page by
> page.
> I found 'rownum' command in oracle document.
> however, the following command is not worked.
>
> select ...
> from ...
> where rownum between 100 and 200;
>
> this command return no rows ....
>
> can somebody help me ....
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Sat Apr 08 2000 - 00:00:00 CDT

Original text of this message

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