Search code examples
sqloracle-databaseinformix

Fetch a fixed number of rows in SQL query in Oracle

Please help me to write an SQL query in the Oracle database. There is table called tbl and it has 12 rows. I want to select first 4 rows first then next 4 and the last 4 rows.

Can any anyone tell me how can I do this in Informix.

Solution

  • You can use rownum:

    select * from (select t.*, rownum rn from tbl t) where rn between 1 and 4;
/
select * from (select t.*, rownum rn from tbl t) where rn between 5 and 8;
/
select * from (select t.*, rownum rn from tbl t) where rn between 9 and 12;
/

    If you're using order by clause then use row_number() (documentation)

    select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 1 and 4;
/
select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 5 and 8;
/
select * from (select t.*, row_number() over (order by column_name) rn from tbl t) where rn between 9 and 12;
/