Oracle - The lowest VARCHAR2 value

Question

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?

Answer 1

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 ...)