This content has been marked as final.
Show 11 replies
-
1. Re: Divide Query Results in block
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 Nov 22, 2012 4:42 PM (in response to 972756)sorry, this is better:1 person found this helpful
select ceil(rownum/5) as id, a from
(select a from test order by 1); -
3. Re: Divide Query Results in block
ranit B Nov 22, 2012 4:49 PM (in response to 970092)Its simple...
givesselect ceil(rownum/5),a from test order by a;
Ranit B.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
Edited by: ranit B on Nov 22, 2012 10:19 PM
-- o/p added -
4. Re: Divide Query Results in block
ranit B Nov 22, 2012 4:47 PM (in response to 972756)Fabio P wrote:
Hi Fabio,
sorry, this is better:
select ceil(rownum/5) as id, a from
(select a from test order by 1);
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 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 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 Nov 22, 2012 4:53 PM (in response to 972756)Fabio P wrote:
Rownum may be unsorted but we are ordering using column 'A'.
Rownum can be unsorted when used in an order by query.
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 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 Nov 22, 2012 4:59 PM (in response to Frank Kulash)Frank Kulash wrote:
Hi Frank,
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 ;
Is row_number() actually required?
Why not just do ROWNUM/5? -
10. Re: Divide Query Results in block
Frank Kulash Nov 22, 2012 5:24 PM (in response to ranit B)Hi,ranit B wrote:
You can do this either with ROWNUM or ROW_NUMBER.
Is row_number() actually required?
Why not just do ROWNUM/5?
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 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 ;)