This discussion is archived
11 Replies Latest reply: Nov 22, 2012 9:16 AM by 972756 RSS

Divide Query Results in block

970092 Newbie
Currently Being Moderated
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
    972756 Newbie
    Currently Being Moderated
    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
    972756 Newbie
    Currently Being Moderated
    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
    ranit B Expert
    Currently Being Moderated
    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
    -- o/p added
  • 4. Re: Divide Query Results in block
    ranit B Expert
    Currently Being Moderated
    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
    972756 Newbie
    Currently Being Moderated
    Rownum can be unsorted when used in an order by query.
  • 6. Re: Divide Query Results in block
    Frank Kulash Guru
    Currently Being Moderated
    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
    ranit B Expert
    Currently Being Moderated
    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
    970092 Newbie
    Currently Being Moderated
    I tested your solutions, they resolve my problem!

    Thank you very much!!
  • 9. Re: Divide Query Results in block
    ranit B Expert
    Currently Being Moderated
    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
    Frank Kulash Guru
    Currently Being Moderated
    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
    972756 Newbie
    Currently Being Moderated
    try this:

    select OBJECT_ID,rownum
    from user_objects
    order by 1;

    You will see what i mean ;)

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points