Reputation: 9024
I need to query a table in the database on a column which is a VARCHAR2
.
I need to retrieve the records in chunks, not all at one go. ROWNUM
is used for this purpose.
The query is like this:
select * from SOMETABLE
where SOMECOLUMN > ?
and rownum <= 100
order by SOMECOLUMN
This query is run repeatedly by changing the SOMECOLUMN
value. To start with, this query should be supplied with the least VARCAHAR2
value so that I get the first 100 records (not in any specific order though). Next time, the SOMECOLUMN
value in the 100th record is used (order by
is added to the query for this purpose) , so that it gets the next 100 records and so on.
(Assumption: SOMECOLUMN
values are unique).
What can be the initial least value supplied to this query?
Upvotes: 1
Views: 184
Reputation: 59652
You can use MIN()
or MAX()
also for VARCHAR2 data, not only for numbers. You can use this one:
with t as
(select SOMETABLE.*, rownum as THE_ROW from SOMETABLE order by SOMECOLUMN)
select *
from t
where THE_ROW between 1 and 100 -- (-> between 101 and 200, between 201 and 300 ...)
Upvotes: 1