Thanks for the idea, Unfortunately I can't do that as the ranges involved are unknown and will be from 1 to several billion at lease. I can't have another table that needs to be augmented each time my ranges change.
Any other ideas? Cheers, Andrew -----Original Message----- From: Paul B van den Berg [mailto:[EMAIL PROTECTED] Sent: Thu 14 April 2005 10:47 To: MySQL Cc: Andrew Braithwaite Subject: Help with a tricky/impossible query... Hi, In SQL you need to define the data that you want to work with: create table z ( z int(5) not null primary key); insert into z values (1),(2),(3),(4),(5),(6),(7),(8),(9),(10),(11),(12),(13),(14),(15),(16),( 17),(18),(19),(20),(21),(22); If you need more values you could use a simple perl looping construct: for ($i=0; $i <= $max; $i++) { $dbh->do( q{ insert into z set z = $i }); } Once you have the table filled, it's easy to explode the x/y ranges by seq: select seq, z from wibble, z where z between x and y Then the rows with seq=1 are: | seq | z | +-----+----+ | 1 | 5 | | 1 | 6 | | 1 | 7 | | 1 | 8 | | 1 | 9 | | 1 | 10 | The rest is as you wanted. Regards, Paul On 14/4/05 1:11 am, "Andrew Braithwaite" <[EMAIL PROTECTED]> wrote: > Here's what I need to do... > > create table wibble( > seq int(3) auto_increment primary key, x int(5), y int(5) ); > > insert into wibble set x=5, y=10; > insert into wibble set x=1, y=3; > insert into wibble set x=17, y=22; > > mysql> select * from wibble; > +-----+------+------+ > | seq | x | y | > +-----+------+------+ > | 1 | 5 | 10 | > | 2 | 1 | 3 | > | 3 | 17 | 22 | > +-----+------+------+ > 3 rows in set (0.09 sec) > > So I want to run a query to explode the x/y ranges by seq. > > The required output is: > > mysql> select some clever things from wibble where some clever stuff > mysql> happens > here; > +-----+------+ > | seq | z | > +-----+------+ > | 1 | 1 | > | 1 | 2 | > | 1 | 3 | > | 1 | 4 | > | 1 | 5 | > | 2 | 1 | > | 2 | 2 | > | 2 | 3 | > | 3 | 17 | > | 3 | 18 | > | 3 | 19 | > | 3 | 20 | > | 3 | 21 | > | 3 | 22 | > +-----+------+ > 14 rows in set (0.17 sec) > > Can anyone help me to achieve this result? > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
