11 Replies Latest reply on Nov 22, 2012 5:16 PM by 972756

    Divide Query Results in block

    970092
    970092 Nov 22, 2012 4:24 PM
      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
          972756 Nov 22, 2012 4:34 PM (in response to 970092)
          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
            972756 Nov 22, 2012 4:42 PM (in response to 972756)
            sorry, this is better:

            select ceil(rownum/5) as id, a from
            (select a from test order by 1);
            1 person found this helpful
            • 3. Re: Divide Query Results in block
              ranit B
              ranit B Nov 22, 2012 4:49 PM (in response to 970092)
              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
                ranit B Nov 22, 2012 4:47 PM (in response to 972756)
                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
                  972756 Nov 22, 2012 4:49 PM (in response to ranit B)
                  Rownum can be unsorted when used in an order by query.
                  • 6. Re: Divide Query Results in block
                    Frank Kulash
                    Frank Kulash Nov 22, 2012 4:51 PM (in response to 970092)
                    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
                      ranit B Nov 22, 2012 4:53 PM (in response to 972756)
                      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
                        970092 Nov 22, 2012 4:55 PM (in response to ranit B)
                        I tested your solutions, they resolve my problem!

                        Thank you very much!!
                        • 9. Re: Divide Query Results in block
                          ranit B
                          ranit B Nov 22, 2012 4:59 PM (in response to Frank Kulash)
                          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
                            Frank Kulash Nov 22, 2012 5:24 PM (in response to ranit B)
                            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
                              972756 Nov 22, 2012 5:16 PM (in response to ranit B)
                              try this:

                              select OBJECT_ID,rownum
                              from user_objects
                              order by 1;

                              You will see what i mean ;)