11 Replies Latest reply: Nov 22, 2012 11:16 AM by 972756

Divide Query Results in block

Hi to everyone,

i have to extract data from a table in block of five records.
For exampe, if i have a table like this:

CREATE TABLE test (a VARCHAR2(1));

Where there are those values:

INSERT INTO test (a) VALUES ('a');
INSERT INTO test (a) VALUES ('b');
INSERT INTO test (a) VALUES ('c');
INSERT INTO test (a) VALUES ('d');
INSERT INTO test (a) VALUES ('e');
INSERT INTO test (a) VALUES ('f');
INSERT INTO test (a) VALUES ('g');
INSERT INTO test (a) VALUES ('h');
INSERT INTO test (a) VALUES ('i');
INSERT INTO test (a) VALUES ('l');
INSERT INTO test (a) VALUES ('m');
INSERT INTO test (a) VALUES ('n');
INSERT INTO test (a) VALUES ('o');

I'd like an output of the query like this:

Pseudo_Column A
1 a
1 b
1 c
1 d
1 e
2 f
2 g
2 h
2 i
2 l
3 m
3 n
3 o

How is it possibile?

Thanks for the help!
Ale
• 1. Re: Divide Query Results in block
Try this:

select FLOOR(rownum/5)+1 as id, a from
(select a from test order by 1);

Edited by: Fabio P on Nov 22, 2012 5:32 PM

Edited by: Fabio P on Nov 22, 2012 5:33 PM
• 2. Re: Divide Query Results in block
sorry, this is better:

select ceil(rownum/5) as id, a from
(select a from test order by 1);
• 3. Re: Divide Query Results in block
Its simple...
``````select ceil(rownum/5),a from test
order by a;``````
gives
``````o/p =
1     a
1     b
1     c
1     d
1     e
2     f
2     g
2     h
2     i
2     l
3     m
3     n
3     o``````
Ranit B.

Edited by: ranit B on Nov 22, 2012 10:19 PM
• 4. Re: Divide Query Results in block
Fabio P wrote:
sorry, this is better:

select ceil(rownum/5) as id, a from
(select a from test order by 1);
Hi Fabio,

I guess the SELECT in FROM clause is not required.
Why don't you directly put order by clause to the main select?

No need of doing 2 selects.
• 5. Re: Divide Query Results in block
Rownum can be unsorted when used in an order by query.
• 6. Re: Divide Query Results in block
Hi,

Using the analytic ROW_NUMBER function instad of ROWNUM, we can avoid the sub-query:
``````SELECT       CEIL ( ROW_NUMBER () OVER (ORDER BY a)
/ 5
)     AS r_num
,       a
FROM       test
ORDER BY  a
;``````
• 7. Re: Divide Query Results in block
Fabio P wrote:
Rownum can be unsorted when used in an order by query.
Rownum may be unsorted but we are ordering using column 'A'.
And, if we order using that... then the values - a,b,c,d... will be sorted and rownum wil be put accordingly.

a - 1
b - 2
c - 3
and so...

I guess there willl never be such an issue.
• 8. Re: Divide Query Results in block
I tested your solutions, they resolve my problem!

Thank you very much!!
• 9. Re: Divide Query Results in block
Frank Kulash wrote:
Hi,

Using the analytic ROW_NUMBER function instad of ROWNUM, we can avoid the sub-query:
``````SELECT       CEIL ( ROW_NUMBER () OVER (ORDER BY a)
/ 5
)     AS r_num
,       a
FROM       test
ORDER BY  a
;``````
Hi Frank,

Is row_number() actually required?
Why not just do ROWNUM/5?
• 10. Re: Divide Query Results in block
Hi,
ranit B wrote:
Is row_number() actually required?
Why not just do ROWNUM/5?
You can do this either with ROWNUM or ROW_NUMBER.

As Fabio pointd out, with ROWNUM, you must use a sub-query if order is important. ROWNUM is assigned in the order that rows are fetched, and, in general, that order is arbitrary and inconsistent. The first 5 rows fetched from the table will not (in general) be the first 5 rows in order by column a (or any other column, for that matter). In fact, the first 5 rows fetched from the table now might not be the first 5 rows fetched from the table if you repeat the exact same query one minute from now. Using a very small table, the first 5 rows fetched will probably be the first 5 rows inserted, but that's not guaranteed. Re-arrange the INSERT statements and see what happens.
The only way to be sure that ROWNUM reflects the order you want is to do an ORDER BY in a sub-query, and then use ROWNUM in the super-query. In that case, the super-query will be fetching rows form the sub-query, not the table, and the ORDER BY clause in the sub-query guarantees that the super-query will fetch the rows in the order you specified.

With ROW_NUMBER, you do not need a sub-query just to display the number.
• 11. Re: Divide Query Results in block
try this:

select OBJECT_ID,rownum
from user_objects
order by 1;

You will see what i mean ;)