Xaprb

Stay curious!

The integers table

with 5 comments

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.

Written by Xaprb

December 7th, 2005 at 11:34 am

Posted in Uncategorized

5 Responses to 'The integers table'

Subscribe to comments with RSS

  1. 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

  2. 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

  3. 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

  4. 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

  5. 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

Leave a Reply