RE: Help with a tricky/impossible query...
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]
Re: Help with a tricky/impossible query...
On Thu, 14 Apr 2005 11:57:50 +0100
Andrew Braithwaite [EMAIL PROTECTED] wrote:
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
I Think you really need the second table, but you can fill it as needed:
my $x=1700;
my $y=2200;
$dbh-do( q{ insert into wibble set x=$x, y=$y });
for ($i=$x; $i =$y ; $i++) {
$dbh-do( q{ insert ignore into z set z = $i });
}
Regards, Paul
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with a tricky/impossible query...
One way would be to build a count table with one column starting with value 1 and incrementing by 1 up to say 500 rows or how many your max y value is. Then just select seq,val from wibble,count where val between x and y create table count (val INT unsigned default '0' not null primary key) -Original Message- From: Andrew Braithwaite [mailto:[EMAIL PROTECTED] Sent: Wednesday, April 13, 2005 7:33 PM To: MySQL Subject: Re: Help with a tricky/impossible query... I should mention that I'm constrained to version 4.0.n so no sub queries for me! Andrew On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I need some help with a tricky query. Before anyone asks, I cannot bring this functionality back to the application layer (as much as I'd like to). 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 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? Thanks, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help with a tricky/impossible query...
I should mention that I'm constrained to version 4.0.n so no sub queries for me! Andrew On 14/4/05 1:11 am, Andrew Braithwaite [EMAIL PROTECTED] wrote: Hi, I need some help with a tricky query. Before anyone asks, I cannot bring this functionality back to the application layer (as much as I'd like to). 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 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? Thanks, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
