Anoop
Anoop

Reputation: 15

Oracle select specific rows

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

Answers (2)

Barbaros Özhan
Barbaros Özhan

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

Grumbunks
Grumbunks

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

Related Questions