The integers table
For the sake of adding cross-references and my own analysis, I’m going to replicate part of someone else’s idea. An integers table can be used to create any desired sequence of numbers, and the idea can be extended to other data types as well. It’s easy to use a mutex table as an integers table, so there’s no need for two tables of the values. You can even create tables with characters, use integers with date functions to generate a range of dates, and so forth. Here is the canonical integers table, and the canonical select from it:
create table integers(i int unsigned not null);
insert into integers(i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
select (hundreds.i * 100) + (tens.i * 10) + units.i as iii
from integers as units
cross join integers as tens
cross join integers as hundreds;
A very useful technique indeed.
I use the integers table in a number of ways in my posts about SQL. Sometimes you’ll see me refer to it as the “numbers” table too.
Thank you for providing the link to “someone else’s idea.” ;o)
[Takes quick look around] hey, fabulous site, and some great contents.
[Bookmarks]
rudy
4 Feb 06 at 3:03 pm
Ah yes, I see you have written articles about this topic too! I forget now who I originally got the idea from, but it’s out there in a number of places (no pun intended). Thanks for the compliment!
Xaprb
5 Aug 06 at 5:45 pm
I came accross this blog trying to learn more about Tally Tables, anyway here something else I found that does what your showing above, but faster if your interested. (Ran in about 1/9th the time for 100,000 numbers) Cool Blog, hope that book is comming well.
DECLARE @StartTime DATETIME –Timer to measure total
SET @StartTime = GETDATE() –Start the timer
SELECT TOP 100000
IDENTITY(INT,1,1) AS N
INTO dbo.Tally
FROM Master.dbo.SysColumns sc1,
Master.dbo.SysColumns sc2
Select * From Tally
Drop table Tally
SELECT STR(DATEDIFF(ms,@StartTime,GETDATE())) ‘ Milliseconds duration’
Jeremy West
7 May 08 at 12:59 pm
Jeremy, there isn’t an identity in mysql, is there?
Xaprb, am not seeing how this plus the code in your page on char-dump work together since your integer table ends up being 0-9. There’s no doubt a better way, but I created a larger integer table via a temporary 0-9 table:
create table Uintegers(i int unsigned not null);
create table integers(i int unsigned not null);
insert into Uintegers(i) values (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);
(like yours so far)
Then, use this table of 9 as workspace to create the needed collection of integers (0-999):
insert into integers (
select (hundreds.i * 100) + (tens.i * 10) + units.i as iii
from Uintegers as units
cross join Uintegers as tens
cross join Uintegers as hundreds );
Then, to clean up:
drop table Uintegers;
sage
29 Sep 08 at 7:36 pm
Hi Baron,
I’m currently in a phase where I abuse information_schema tables for optimizations very savage. I had the idea for the first time, when I wanted to generate 10 random numbers from 1 to auto_increment of a table:
SELECT 1 + FLOOR(RAND() * auto_increment) start
FROM information_schema.global_status
JOIN (
SELECT auto_increment
FROM information_schema.tables
WHERE table_schema=DATABASE()
AND table_name=’t1′
)X
LIMIT 10;
Combined with the idea of your cross-join solution, one could use something like this instead of having a temporary integer table:
SELECT @x:= @x + 1
FROM (
SELECT @x:= 0
) TMP
JOIN (
SELECT *
FROM information_schema.global_status
LIMIT 10
) X
CROSS JOIN (
SELECT *
FROM information_schema.global_status
LIMIT 10
) Y
CROSS JOIN (
SELECT *
FROM information_schema.global_status
LIMIT 10
) Z;
I used global_status, because this table has about 300 elements, which allows to change the LIMIT in a very constant and also large range.
Robert
Robert Eisele
11 Oct 11 at 7:13 pm