1

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.

Amit Raj
  • 1,358
  • 3
  • 22
  • 47

3 Answers3

2

EDIT: now should be fixed with 3-level select:

select * from (
  select q1.*, rownum as rn from (   --get correct rownum 
      select * from tbl order by column --get correct order
  ) q1
) q2
 where q2.rn between 1 and 4; -- filter

for first part.

For second and third part:

 where q2.rn between 5 and 8
 where q2.rn between 9 and 12
vasja
  • 4,732
  • 13
  • 15
  • 1
    In your case `rownum between 5 and 8` and `rownum between 9 and 12` will not work, because values greater than positive integer are always false, as stated in [Oracle document](http://docs.oracle.com/cd/B19306_01/server.102/b14200/pseudocolumns009.htm) – yamny Sep 10 '14 at 11:27
  • @vasja- yammy is right. it not working for second and third part. – Amit Raj Sep 10 '14 at 11:39
  • ROWNUM doesn't work that way. ROWNUM beiing a pseudo-column, it will only increment further to 2 when it is at 1. So, when you say rownum between 5 and 8, ROWNUM doesn't even increment, so it will NEVER return any rows. It will only work in this way : `select * from(select rownum rn, val from order_test order by val) where rn between 5 and 8` – Lalit Kumar B Sep 10 '14 at 11:45
  • @vasja- yes, it is also working – Amit Raj Sep 10 '14 at 12:23
  • You can simply use the query I posted in above comment :-) – Lalit Kumar B Sep 10 '14 at 13:23
  • @Latit - I believe you shouldn't use ORDER BY and ROWNUM in same query to get correct results because ROWNUM is avaluated BEFORE ordering of results (see http://www.orafaq.com/wiki/ROWNUM). So shouldn't simplify like you proposed (though syntactically ok). – vasja Sep 10 '14 at 13:27
2

There is nothing called as first rows, last rows, "n" rows unless you explicitly specify an ORDER BY and then select the required rows.

Top-n Row Limiting feature in Oracle 12c on ward:

SQL> select * from order_test order by val;

       VAL
----------
         1
         1
         2
         2
         3
         3
         4
         4
         5
         5
         6
         6
         7
         7
         8
         8
         9
         9
        10
        10

20 rows selected.

First 4 rows :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  FETCH FIRST 4 ROWS ONLY;

       VAL
----------
         1
         1
         2
         2

Next 4 rows(look at OFFSET) :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 4 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         3
         3
         4
         4

Finally, next 4 rows with OFFSET 8 rows :

SQL> SELECT val
  2  FROM   order_test
  3  ORDER BY VAL
  4  OFFSET 8 ROWS FETCH NEXT 4 ROWS ONLY;

       VAL
----------
         5
         5
         6
         6
Lalit Kumar B
  • 47,486
  • 13
  • 97
  • 124
1

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;
/
yamny
  • 660
  • 4
  • 13
  • @yammy- Thanks, it works for me. Can you tell me how can I put where condition in the above query. – Amit Raj Sep 10 '14 at 12:12
  • 1
    @AmitRaj You mean `where` for result filtering? You can add additional conditions after `where rn...` or inside subquery after `from tbl t where ...` (i recommend to put in inside subquery) – yamny Sep 10 '14 at 12:24