85

I need to do a join with a table/result-set/whatever that has the integers n to m inclusive. Is there a trivial way to get that without just building the table?

(BTW what would that type of construct be called, a "Meta query"?)

m-n is bounded to something reasonable ( < 1000's)

Improve this question
1
  • you can have troubles with auto_increment when need include parent and childs at same time, i never use it, nextval is simplier. You can see nextval function for mysql code here: stackoverflow.com/questions/8058675/… Commented Nov 11, 2011 at 11:02

19 Answers 19

Reset to default
123

I found this solution on the web 

SET @row := 0;
SELECT @row := @row + 1 as row, t.*
FROM some_table t, (SELECT @row := 0) r

Single query, fast, and does exactly what I wanted: now I can "number" the "selections" found from a complex query with unique numbers starting at 1 and incrementing once for each row in the result.

I think this will also work for the issue listed above: adjust the initial starting value for @row and add a limit clause to set the maximum.

BTW: I think that the "r" is not really needed.

ddsp

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

yes r is needed - Error 1248: every derived table must have its own alias
The number of rows selected comes from some_table not r. If some_table has no rows, you get nothing. I used this to generated a set of test data which just needed names and passwords. The sequence of numbers became the names and the password was just ENCRYPT('passwd') so they'd all be the same. To generate rows, I selected from another table, but didn't actually select any of the columns. It just gave me a row for each row in some_table, all of them with sequential numbers.
+Many, I'm a bit in shock - had no idea MySQL could do this. These two separate queries work, for example, for re-sequencing a subset of a table: SET @row := 0 and UPDATE foo SET position = (@row := @row + 1) WHERE <conditions> ORDER BY last_modified ASC
Executing this from C# gives the error, "Parameter '@row' must be defined." But I found a solution here: stackoverflow.com/questions/958953/…
@Izkata, David, Is this behavior guaranteed by the specs? For all you know, a future version could give us the same value for all rows and only do the increment after the entire selection.
43

If you happen to be using the MariaDB fork of MySQL, the SEQUENCE engine allows direct generation of number sequences. It does this by using virtual (fake) one column tables.

For example, to generate the sequence of integers from 1 to 1000, do this

     SELECT seq FROM seq_1_to_1000;

For 0 to 11, do this.

     SELECT seq FROM seq_0_to_11;

For a week's worth of consecutive DATE values starting today, do this.

SELECT FROM_DAYS(seq + TO_DAYS(CURDATE)) dateseq FROM seq_0_to_6

For a decade's worth of consecutive DATE values starting with '2010-01-01' do this.

SELECT FROM_DAYS(seq + TO_DAYS('2010-01-01')) dateseq
  FROM seq_0_to_3800
 WHERE FROM_DAYS(seq + TO_DAYS('2010-01-01')) < '2010-01-01' + INTERVAL 10 YEAR

If you don't happen to be using MariaDB, please consider it.

Improve this answer

3 Comments

I tried this here: sqlzoo.net/wiki/Guest_House_Assessment_Hard and it didn't work. However it is highly likely that because in-browser we are working with a wrapped MariaDB then this is not working for only this reason
This is an amazing solution that allows you to do many thinks like calculate the projected revenue based on a recurring schedule that runs on a daily, weekly, monthly, yearly, biweekly, etc. frequency.
Nice article about sequence generation in Percona fork and more: percona.com/blog/2020/07/27/…
39

The following will return 1..10000 and is not so slow

SELECT @row := @row + 1 AS row FROM 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t,
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3, 
(select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4, 
(SELECT @row:=0) numbers;
Improve this answer

6 Comments

an explanation would be nice - t - t4 each contain 10 rows of dummy data, cartesian product of these is 10^4, i.e. 10000 rows of dummy data, and the outer select is just mysql version of rownumber
@Aprillion, 2^14 will do the same thing. With only 28 values instead of 40.
You could get rid of the variable declaration by replacing "@row := @row + 1 as row" with "concat(t.0,t2.0,t3.0,t4.0) + 1 as row", then sorting by "row". To get get an accurate result you would also need to change the duplicate "select 6" in each subquery to "select 2" ("2" isn't currently used but "6" is listed twice.). As a bonus, you could then also shorten each "union all" to "union", since there should no longer be any duplicate rows.
@Seth McCauley: no need to change 6 to 2. the values aren't even used. might as well all be null. only the @row := @row + 1 as row is needed to get the incrementing number per row. the unions just multiply to give the number of rows.
I had to change AS row to AS r to prevent a syntax error.
|
23

try this.. it works for me in mysql version 8.0. you can modify below query according to your required range

WITH recursive numbers AS (
    select 0 as Date
   union all
   select Date + 1
   from numbers
   where Date < 10)
select * from numbers;

and yes without creating a table as mentioned in your post

Improve this answer

1 Comment

Nice solution, however, this works for numbers less than 1000 which is the default value of cte_max_recursion_depth
11

There is no sequence number generator (CREATE SEQUENCE) in MySQL. Closest thing is AUTO_INCREMENT, which can help you construct the table.

Improve this answer

2 Comments

Up to now MySQL 8 does not support this feature. It was implemented in MariaDB and PostgreSQL.
Interestingly, I tested out using AUTO_INCREMENT to construct a table in MySQL 5.7, and while this seems like it should work, it ends up skipping digits if you try to insert a bunch of entries into the table at once. test code. I was pretty surprised to see this behavior. I'm not sure what I'm doing in my test that's causing this.
9

Sequence of numbers between 1 and 100.000:

SELECT e*10000+d*1000+c*100+b*10+a n FROM
(select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
(select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
(select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
(select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
(select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5
order by 1

I use it to audit if some number is out of sequence, something like this:

select * from (
    select 121 id
    union all select 123
    union all select 125
    union all select 126
    union all select 127
    union all select 128
    union all select 129
) a
right join (
    SELECT e*10000+d*1000+c*100+b*10+a n FROM
    (select 0 a union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t1,
    (select 0 b union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t2,
    (select 0 c union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t3,
    (select 0 d union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t4,
    (select 0 e union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9) t5
    order by 1
) seq on seq.n=a.id
where seq.n between 121 and 129
and   id is null

The result will be the gap of number 122 and 124 of sequence between 121 and 129:

id     n
----   ---
null   122
null   124

Maybe it helps someone!

Improve this answer

1 Comment

I ran the query without "order by 1", and the query produced numbers that were not in sequential order. I would have written the query with "order by n". "Order by 1" seems meaningless and yet causes the query to order the integers correctly. Why does it work? Why is "order by 1" preferred over "order by n"?
6

The simplest way to do this is:

SET @seq := 0;
SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM your_table yt;

or in one query:

SELECT @seq := FLOOR(@seq + 1) AS sequence, yt.*
FROM (SELECT @seq := 0) s, your_table yt;

The FLOOR() function is used here to get an INTEGER in place of a FLOAT. Sometimes it is needed.

My answer was inspired by David Poor answer. Thanks David!

Improve this answer

1 Comment

Helpful, Thanks! I searched for that to fill my first field in my result from get_records_sql (in moodle) "to make the first column something unique". Works well.
5

There is a way to get a range of values in a single query, but its a bit slow. It can be sped up by using cache tables.

assume you want a select with a range of all BOOLEAN values:

SELECT 0 as b UNION SELECT 1 as b;

we can make a view

CREATE VIEW ViewBoolean AS SELECT 0 as b UNION SELECT 1 as b;

then you can do a Byte by

CREATE VIEW ViewByteValues AS
SELECT b0.b + b1.b*2 + b2.b*4 + b3.b*8 + b4.b*16 + b5.b*32 + b6.b*64 + b7.b*128 as v FROM
ViewBoolean b0,ViewBoolean b1,ViewBoolean b2,ViewBoolean b3,ViewBoolean b4,ViewBoolean b5,ViewBoolean b6,ViewBoolean b7;

then you can do a

CREATE VIEW ViewInt16 AS
SELECT b0.v + b1.v*256 as v FROM
ViewByteValues b0,ViewByteValues b1;

then you can do a

SELECT v+MIN as x FROM ViewInt16 WHERE v<MAX-MIN;

To speed this up I skipped the auto-calculation of byte values and made myself a

CREATE VIEW ViewByteValues AS
SELECT 0 as v UNION SELECT 1 as v UNION SELECT ...
...
...254 as v UNION SELECT 255 as v;

If you need a range of dates you can do.

SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE v<NumDays;

or

SELECT DATE_ADD('start_date',v) as day FROM ViewInt16 WHERE day<'end_date';

you might be able to speed this up with the slightly faster MAKEDATE function

SELECT MAKEDATE(start_year,1+v) as day FRON ViewInt16 WHERE day>'start_date' AND day<'end_date';

Please note that this tricks are VERY SLOW and only allow the creation of FINITE sequences in a pre-defined domain (for example int16 = 0...65536 )

I am sure you can modify the queries a bit to speed things up by hinting to MySQL where to stop calculating ;) (using ON clauses instead of WHERE clauses and stuff like that)

For example:

SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0,
ViewByteValues b1,
ViewByteValues b2,
ViewByteValues b3
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < MAX-MIN;

will keep your SQL server busy for a few hours

However

SELECT MIN + (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) FROM
ViewByteValues b0
INNER JOIN ViewByteValues b1 ON (b1.v*256<(MAX-MIN))
INNER JOIN ViewByteValues b2 ON (b2.v*65536<(MAX-MIN))
INNER JOIN ViewByteValues b3 ON (b3.v*16777216<(MAX-MIN)
WHERE (b0.v + b1.v*256 + b2.v*65536 + b3.v*16777216) < (MAX-MIN);

will run reasonably fast - even if MAX-MIN is huge as long as you limit the result with LIMIT 1,30 or something. a COUNT(*) however will take ages and if you make the mistake of adding ORDER BY when MAX-MIN is bigger than say 100k it will again take several seconds to calculate...

Improve this answer

Comments

5

You could try something like this:

SELECT @rn:=@rn+1 as n
FROM (select @rn:=2)t, `order` rows_1, `order` rows_2 --, rows_n as needed...
LIMIT 4

Where order is just en example of some table with a reasonably large set of rows.

Edit: The original answer was wrong, and any credit should go to David Poor who provided a working example of the same concept

Improve this answer

5 Comments

I'm going to have to puzzle that one out but it sure looks neat!
Basically the inner most select initializes a session var to 2 then the outer select increment this var for each row, in this case the cartesian join to order means the var will get a relation with a sequence of numbers from 2 to the number of orders + 2 (limit 4 restricts this to 4 numbers)
This "solution" appears to require an existing table order containing the complete list of possible matching integers. I cannot get anything other than a single row result if I try running without joining to a large pre-existing table with valid integers already in it.
@rektide, It does not "appear". It is wrong and does not work at all.
Oh, this was a long time ago. I think my intention was "order" as in Purchase Order (I was doing ERP-systems at the time, and in that kind of system such a table could be expected to be reasonably large). But no I don't think my intention was to use actual integers from that table. I'll see if I can improve it a bit.
5

Counter from 1 to 1000:

  • no need to create a table
  • time to execute ~ 0.0014 sec
  • can be converted into a view
    select tt.row from
    (
    SELECT cast( concat(t.0,t2.0,t3.0) + 1 As UNSIGNED) as 'row' FROM 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t,
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t2, 
    (select 0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t3
    ) tt
    order by tt.row

Credits: answer, comment by Seth McCauley below the answer.

Improve this answer

Comments

4

You appear to be able to construct reasonably large sets with:

select 9 colname union all select 10 union all select 11 union all select 12 union all select 13 ...

I got a parser stack overflow in the 5300's, on 5.0.51a.

Improve this answer

3 Comments

WOW, just WOW! you actually tested that?! Wow!
@BCS, Type this in your url bar: data:text/html,<script>for(var x=1;x<=5678;++x)document.write((x===1?'':' union all ')+'select '+x);</script>
@Pacerier Great technique,not just for this application.
4

As of Mariadb 10.2 (and MySQL has it too now), you can do this very elegantly with a Common Table Expression (CTE).

Here we go:

with recursive numbers (n) as (
  select 1 as n
  union
  select n+1 from numbers where n<100
)
select * from numbers

Of course, you can now join this numbers CTE with your own tables, and you've got your 1..n (or n..m by the same approach).

Improve this answer

Comments

3

Here is a compact binary version of the technique used in other answers here:

select ((((((b7.0 << 1 | b6.0) << 1 | b5.0) << 1 | b4.0) 
                  << 1 | b3.0) << 1 | b2.0) << 1 | b1.0) << 1 | b0.0 as n
from (select 0 union all select 1) as b0,
     (select 0 union all select 1) as b1,
     (select 0 union all select 1) as b2,
     (select 0 union all select 1) as b3,
     (select 0 union all select 1) as b4,
     (select 0 union all select 1) as b5,
     (select 0 union all select 1) as b6,
     (select 0 union all select 1) as b7

There are no unique or sorting phases, no string to number conversion, no arithmetic operations, and each dummy table only has 2 rows, so it should be pretty fast.

This version uses 8 "bits" so it counts from 0 to 255, but you can easily tweak that.

Improve this answer

Comments

3

This query generates numbers from 0 to 1023. I believe it would work in any sql database flavor:

select
     i0.i
    +i1.i*2
    +i2.i*4
    +i3.i*8
    +i4.i*16
    +i5.i*32
    +i6.i*64
    +i7.i*128
    +i8.i*256
    +i9.i*512
    as i
from
               (select 0 as i union select 1) as i0
    cross join (select 0 as i union select 1) as i1
    cross join (select 0 as i union select 1) as i2
    cross join (select 0 as i union select 1) as i3
    cross join (select 0 as i union select 1) as i4
    cross join (select 0 as i union select 1) as i5
    cross join (select 0 as i union select 1) as i6
    cross join (select 0 as i union select 1) as i7
    cross join (select 0 as i union select 1) as i8
    cross join (select 0 as i union select 1) as i9
Improve this answer

Comments

2

How big is m?

You could do something like:

create table two select null foo union all select null;
create temporary table seq ( foo int primary key auto_increment ) auto_increment=9 select a.foo from two a, two b, two c, two d;
select * from seq where foo <= 23;

where the auto_increment is set to n and the where clause compares to m and the number of times the two table is repeated is at least ceil(log(m-n+1)/log(2)).

(The non-temporary two table could be omitted by replacing two with (select null foo union all select null) in the create temporary table seq.)

Improve this answer

1 Comment

I kind of like the concept, but if I have to build the table anyway, I'll just do an auto increment and add rows manually till it's big enough.
2

try with following

select
ROW_NUMBER() OVER (ORDER BY id asc) AS 'rowId', otherField from table;
Improve this answer

1 Comment

This does not provide an answer to the question. Once you have sufficient reputation you will be able to comment on any post; instead, provide answers that don't require clarification from the asker. - From Review
1

Warning: if you insert numbers one row at a time, you'll end up executing N commands where N is the number of rows you need to insert.

You can get this down to O(log N) by using a temporary table (see below for inserting numbers from 10000 to 10699):

mysql> CREATE TABLE `tmp_keys` (`k` INTEGER UNSIGNED, PRIMARY KEY (`k`));
Query OK, 0 rows affected (0.11 sec)

mysql> INSERT INTO `tmp_keys` VALUES (0),(1),(2),(3),(4),(5),(6),(7);
Query OK, 8 rows affected (0.03 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+8 from `tmp_keys`;
Query OK, 8 rows affected (0.02 sec)
Records: 8  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+16 from `tmp_keys`;
Query OK, 16 rows affected (0.03 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+32 from `tmp_keys`;
Query OK, 32 rows affected (0.03 sec)
Records: 32  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+64 from `tmp_keys`;
Query OK, 64 rows affected (0.03 sec)
Records: 64  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+128 from `tmp_keys`;
Query OK, 128 rows affected (0.05 sec)
Records: 128  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+256 from `tmp_keys`;
Query OK, 256 rows affected (0.03 sec)
Records: 256  Duplicates: 0  Warnings: 0

mysql> INSERT INTO `tmp_keys` SELECT k+512 from `tmp_keys`;
Query OK, 512 rows affected (0.11 sec)
Records: 512  Duplicates: 0  Warnings: 0

mysql> INSERT INTO inttable SELECT k+10000 FROM `tmp_keys` WHERE k<700;
Query OK, 700 rows affected (0.16 sec)
Records: 700  Duplicates: 0  Warnings: 0

edit: fyi, unfortunately this won't work with a true temporary table with MySQL 5.0 as it can't insert into itself (you could bounce back and forth between two temporary tables).

edit: You could use a MEMORY storage engine to prevent this from actually being a drain on the "real" database. I wonder if someone has developed a "NUMBERS" virtual storage engine to instantiate virtual storage to create sequences such as this. (alas, nonportable outside MySQL)

Improve this answer

4 Comments

a nice KISS solution. OTOH it does materialize the whole thing ;(. +1
Yeah. It's too bad there's not a SELECT number FROM INTEGERS WHERE number > 0 AND number < 10000... but technically I suppose someone could come up with a virtual storage engine that does this.
hmmmm... somebody -1'd this answer, any particular reason why?
...and it took someone 5 years to figure out why?
1 
with t1 as (
select 0 union all select 1 union all select 2 union all select 3 union all select 4 union all select 5 union all select 6 union all select 7 union all select 8 union all select 9
)
SELECT ROW_NUMBER() over ()
FROM
t1,
t1 as t2;

Can continue aliasing t1 table for however large of a table you want (10 to the nth power). And then can add a limit X to cut it off.

Improve this answer

Comments

0

If you were using Oracle, 'pipelined functions' would be the way to go. Unfortunately, MySQL has no such construct.

Depending on the scale of the numbers you want sets of, I see two simple ways to go : you either populate a temporary table with just the numbers you need (possibly using memory tables populated by a stored procedure) for a single query or, up front, you build a big table that counts from 1 to 1,000,000 and select bounded regions of it.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.