Home » SQL & PL/SQL » SQL & PL/SQL » Series of numbers
Series of numbers [message #246852] Fri, 22 June 2007 04:38 Go to next message
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 #246859 is a reply to message #246852] Fri, 22 June 2007 04:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Here's a clue:
SQL> select rownum from dual connect by level <= 5;

    ROWNUM
----------
         1
         2
         3
         4
         5

5 rows selected.

Regards
Michel
Re: Series of numbers [message #246862 is a reply to message #246852] Fri, 22 June 2007 04:50 Go to previous messageGo to next message
Narfix
Messages: 5
Registered: June 2007
Junior Member
Ohhh... why didn't I think of that...
Thanks a lot !!
Re: Series of numbers [message #246866 is a reply to message #246852] Fri, 22 June 2007 04:55 Go to previous messageGo to next message
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 #246870 is a reply to message #246866] Fri, 22 June 2007 04:59 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Afaik, no limit but memory.

Regards
Michel
Re: Series of numbers [message #246874 is a reply to message #246852] Fri, 22 June 2007 06:01 Go to previous messageGo to next message
Narfix
Messages: 5
Registered: June 2007
Junior Member
I'll have to go with some VB code then..

Thank you for the help

[Updated on: Fri, 22 June 2007 06:01]

Report message to a moderator

Re: Series of numbers [message #246876 is a reply to message #246870] Fri, 22 June 2007 06:02 Go to previous messageGo to next message
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) where
SELECT level FROM DUAL CONNECT BY level <= n
won't always work.
You need to use
SELECT 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 Go to previous messageGo to next message
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 #246891 is a reply to message #246876] Fri, 22 June 2007 06:50 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes.
For 10g:
SELECT rownum rn FROM DUAL CONNECT BY level <= n

For 9i:
SELECT rn FROM (SELECT rownum rn FROM DUAL CONNECT BY level <= n)

For 8*:
SELECT rownum rn from (select 1 from dual group by cube(1,2,...,<log2(N+1))) where rownum <= N


Regards
Michel
Re: Series of numbers [message #246911 is a reply to message #246891] Fri, 22 June 2007 07:50 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Michel Cadot wrote on Fri, 22 June 2007 13:50

For 8*:
SELECT rownum rn from (select 1 from dual group by cube(1,2,...,<log2(N+1))) where rownum <= N

I once killed our Development database with a GROUP BY CUBE select from dual. It was fun Very Happy

MHE
Re: Series of numbers [message #246913 is a reply to message #246911] Fri, 22 June 2007 07:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes, don't use more than 10 in the cube.
After that, cartesian join several cubes.
Cube execution resource consumption doubles with each number.

Regards
Michel
Re: Series of numbers [message #246915 is a reply to message #246913] Fri, 22 June 2007 08:01 Go to previous messageGo to next message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
I think I tried about 15 numbers in the cube...bad idea. Embarassed

MHE
Re: Series of numbers [message #246920 is a reply to message #246915] Fri, 22 June 2007 08:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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
Re: Series of numbers [message #246926 is a reply to message #246920] Fri, 22 June 2007 08:32 Go to previous message
Maaher
Messages: 7062
Registered: December 2001
Senior Member
Laughing I knew you were going to do that!

And thank you for using code tags! Very Happy

MHE
Previous Topic: REGEXP - ignore spaces
Next Topic: Inserting values
Goto Forum:
  


Current Time: Sat Dec 03 16:27:06 CST 2016

Total time taken to generate the page: 0.08668 seconds