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

    Divide Query Results in block

    970092
      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
          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
            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
              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
                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
                  Rownum can be unsorted when used in an order by query.
                  • 6. Re: Divide Query Results in block
                    Frank Kulash
                    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
                      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
                        I tested your solutions, they resolve my problem!

                        Thank you very much!!
                        • 9. Re: Divide Query Results in block
                          ranit B
                          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
                            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
                              try this:

                              select OBJECT_ID,rownum
                              from user_objects
                              order by 1;

                              You will see what i mean ;)