CODEWITHSUNDEEP
oracle-databasevarchar2
Manikandan Kannan
Manikandan Kannan

Reputation: 9024

Oracle - The lowest VARCHAR2 value

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

Answers (1)

Wernfried Domscheit
Wernfried Domscheit

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

Related Questions