Reputation: 15
Can we select specific rows to range in oracle? for example, I have a table of 100 rows I have to select only a range of 10 to 20-row numbers. Is it possible to do that
Upvotes: 0
Views: 3424
Reputation: 65408
You can do with an auxiliary operation. Firstly number the rows by row_number()
function and then order by them :
select * from
(
select row_number() over (order by 0) rn, t.*
from tab t
)
where rn between 10 and 20;
but this is not a stable operation, since SQL statements are unordered sets. Therefore it's better to define a unique identity column and order depending on it.
Replace zero in the order by clause with some columns of your table to be able to reach a rigid ordering criteria. If a primary key column exists, it might be better to include only it in the order by list.
Upvotes: 2
Reputation: 1277
would LIMIT and OFFSET work? ie.
SELECT * FROM table
LIMIT 20
OFFSET 20
will read rows 20 -> 40. Is this what you are trying to do?
Upvotes: 0