Home » SQL & PL/SQL » SQL & PL/SQL » Series of numbers
| Series of numbers [message #246852] |
Fri, 22 June 2007 04:38  |
Narfix
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
Hello !
I'd like to know if there's a way to get a serie of values within 2 boundaries.
I have 2 colums MIN & MAX and would like to get all the values between them.
I thought maybe you could do a select [min-max] from dual but that doesn't seem to exist.
Any idea ?
Thanks a lot !!
|
|
|
|
|
|
|
|
| Re: Series of numbers [message #246866 is a reply to message #246852] |
Fri, 22 June 2007 04:55  |
Narfix
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
Ooops some more questions
Is there a limit with 100 rows ?
I can't get any higher than 100 rows and I'll ultimately need 500 of them.
Also this throws an error :
select rownum from dual connect by level <= (select max(rubvmax) from porrub)
A huge thank for your help
|
|
|
|
|
|
|
|
| Re: Series of numbers [message #246876 is a reply to message #246870] |
Fri, 22 June 2007 06:02 |
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
|
Senior Member |
|
|
There is a slight problem in some versions of Oracle (<10g if I remember correctly) whereSELECT level FROM DUAL CONNECT BY level <= n
You need to useSELECT level
FROM (SELECT level FROM DUAL CONNECT BY level <= n) |
|
|
|
| Re: Series of numbers [message #246877 is a reply to message #246852] |
Fri, 22 June 2007 06:05 |
Narfix
Messages: 5
Registered: June 2007
|
Junior Member |
|
|
Wooo just when my hope was about to vanish !!
This works like a charm !!
Updated to
SELECT lvl
FROM (SELECT rownum lvl FROM DUAL CONNECT BY level <= 500)
Thanks !!
|
|
|
|
|
|
|
|
|
|
|
|
| Re: Series of numbers [message #246920 is a reply to message #246915] |
Fri, 22 June 2007 08:15 |
 |
Michel Cadot
Messages: 68722
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10));
1024 rows selected.
Elapsed: 00:00:00.05
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11));
2048 rows selected.
Elapsed: 00:00:00.12
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12));
4096 rows selected.
Elapsed: 00:00:00.21
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13));
8192 rows selected.
Elapsed: 00:00:00.47
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14));
16384 rows selected.
Elapsed: 00:00:01.02
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15));
32768 rows selected.
Elapsed: 00:00:01.79
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16));
65536 rows selected.
Elapsed: 00:00:03.45
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17));
131072 rows selected.
Elapsed: 00:00:07.77
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18));
262144 rows selected.
Elapsed: 00:00:15.11
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19));
524288 rows selected.
Elapsed: 00:00:29.83
SQL> SELECT rownum rn from (select 1 from dual group by cube(1,2,3,4,5,6,7,8,9,10,11,12,13,14,15,16,17,18,19,20));
1048576 rows selected.
Elapsed: 00:01:11.96
Regards
Michel
|
|
|
|
|
|
Goto Forum:
Current Time: Fri Dec 13 05:48:01 CST 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
I knew you were going to do that!