CODEWITHSUNDEEP
sqldatabaseoracle-database
Abdul
Abdul

Reputation: 587

Oracle Row fetch within limit

I want to fetch data between 10000 to 20000 rownum i have made this query

SELECT xml_to_string(XMLRECORD) FROM TABLENAME WHERE ROWNUM >10000 AND ROWNUM<=20000

but above query is not working but when i change query to

SELECT xml_to_string(XMLRECORD) FROM TABLENAME WHERE ROWNUM >0 AND ROWNUM<=20000 it works fine

what am i missing..?

Upvotes: 0

Views: 1094

Answers (2)

smn_onrocks
smn_onrocks

Reputation: 1342

rownum doesnot stay in any table It generates run time so when ever you run any sql statement then only for that time rownum will generate so you can't expect that you can use row num in between close.

Upvotes: 1

Multisync
Multisync

Reputation: 8787

Try this:

SELECT xml_to_string(XMLRECORD) FROM (select t.*, rownum rw from TABLENAME t) 
WHERE  rw>10000 AND rw<=20000

Rownum is calculated when Oracle retrieves the result of the query. That's why a query select * from some_table where rownum > 1 never returns anything.

In addition, without ORDER BY it doesn't make sense to get rows between 10000 and 20000. You might as well get the first 10000 (as rows are unsorted the result is unpredictable - any row can be the first).

From Oracle documentation:

For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows. The first row selected has a ROWNUM of 1, the second has 2, and so on.

Conditions testing for ROWNUM values greater than a positive integer are always false. For example, this query returns no rows:

SELECT *
FROM employees
WHERE ROWNUM > 1;

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 makes the condition false. All rows subsequently fail to satisfy the condition, so no rows are returned.

Upvotes: 2

Related Questions