How to query from somewhere in the middle of table

[skipizza]

How does one query starting from the middle of a table?

Suppose there is table with 500 rows and the first 250 rows were selected.
SELECT * FROM table1 WHERE rownum < 251;

How can the second 250 rows be selected?

How does one select the 300th to 399th row?

 

[elbdotcom]

I never done this but here is a way to do that by using an
inline view.

SELECT OT.*
FROM (SELECT ROWNUM RR, IT.*
FROM table1 IT) OT
WHERE OT.RR BETWEEN 300 AND 399;

 

[Guest_imported]

If your table, co_names, had the column ename in it and there were 500 rows, try this

select ename from co_names
where rowid not in ( select rowid from enames where rownum < 251 )
and rownum < 251

 

[Guest_imported]

If your table, co_names, had the column ename in it and there were
500 rows, try this

select ename from co_names
where rowid not in ( select rowid from CO_NAMES where rownum < 251 )
and rownum < 251

Wrong table name in previous message ( co_names )

 

[carp]

However, keep in mind that Oracle does not guarantee that two executions of the same query will return the rows in the same order!

 

[skipizza]

Thanks, both methods work. The inline view works well on a very large table, too.

 

[skipizza]

carp,
I think most of the time it does. I suppose Oracle does not guarantee the same order every time because some select statments process in parallel.

 

[carp]

I also believe it's because the order retrieved can also depend on which data blocks are already in the buffer cache (and there may be other factors I haven't read/heard/thought about). However, my experience concurs with your take - &quot;most of the time it does&quot;.

 

[stevecal]

We had some problems with this as I recall, for precisely the reasons set out above. The rows //cannot// be guaranteed.

The smart thing to do if you really //must// have your stuff in order and guarantee their integrity is to bolt a column onto your table and put a sequence number into it (you can do this with a trigger, in your update sp or with a cursor loop as and when required). This means all your rows can be correctly and uniquely identified.