Very Computer

You'll probably get a more technical answer, but in essence 'rownum'
is not a value in the table and when you use it in a where statement (
other that a < , which sqlplus handles as a special case ) the query
fails.

Rownum is assigned as the record is returned and sqlplus is able to
read it and compare it to the < operator's value and stop when it has
been  reached - no other operator works with rownum ( you can't say
rownum > 10 for instance )

Hello all,

        When I type "select A from AAA" then 100 rows selected.
        Now, I want to select the first 10 records. So I type "select A from
AAA where rownum > 0 and rownum <= 10", then 10 rows selected.
        Now, I want to select the NEXT 10 record by "select A from AAA where
rownum > 10 and rownum <= 20", then no row selected.

        Could anyone tell me why no row selected? And how to select the NEXT 10
records by a SQL statement?

Thanks,
Jimmy

The problem is that ROWNUM is assigned sequentially to each row that meets
the criteria. Oracle's SQL Language Reference Manual explains why no rows
are selected when you test for ROWNUM values greater than 1 (i.e. ROWNUM >
10):

 "The first row fetched is assigned a ROWNUM of 1 and makes the condition
false. The second row to be fetched is now the first row and is also
assigned a ROWNUM of 1 and also makes the condition false."

As to how you can retreive the next 10 rows, the easy way is to use PL/SQL,
but I suspect that's not what you want. I have never tried this in SQL, so I
have absolutely no idea whether it'll work, but you could try the following:

SELECT ROWNUM, MAX(a)
FROM aaa
GROUP BY ROWNUM
HAVING ROWNUM > 10

...you can use a view to do this, try the following:

create view vwtest as select rownum the_rownum,name from yourcolumn

...now you can make a 'subselection' with the query:

select * from vwtest where the_rownum between 10 and 20

However, now you probably will run into problems when you want to retrieve a
sorted selection.

Succes anyway

A previous reply had a way to do it,
in case you missed it it involves using the 'minus' operator:

select * from table where rownum < 20....
minus
select * from table where <= 10

This will return rownums 11-20

Hope it helps,
John Greco
Oracle DBA

PS
The 'minus','intersect' and 'union' operators are underused and can be
very powerful for 'odd' queries that might otherwise require PL/SQL to
get the result you want.