How to generate a table in SQL having sequence numbers from 0 to a max value

Question

I am currently using

SET @startnum = 0;
SET @endnum = 10;

WITH n AS (
SELECT @startnum AS num
UNION ALL
SELECT @startnum +1 FROM n WHERE @startnum < @endnum
)
SELECT num FROM n ORDER BY num;

But the version of SQL that I am using doesn't support "With table" query. Also, I am not able to use row_number() or rank_over() partition by functions.

mySQL workbench 6.2 2008 version – Dgstah Nov 23 '16 at 17:50 — Dgstah, Nov 23 '16 at 17:50

score 0 · Answer 1 · answered Nov 23 '16 at 14:55

0

If I need such a table, I do the following:

create table t (int col1);
insert into t values (1);
create view v as select max(col1) as mcol1 from t;

and then as often as required:

insert into t
select col1 + mcol1
  from t, v
 where col1 + mcol1 <= NUMBEROFROWSDESIRED;

This will double your table on each execution

answered Nov 23 '16 at 14:55

Ronald

2,842
16
16

This step is effective, but I will have to execute the query manually, whereas the numberofrowsdesired is also an automated number. Is there any alternative method? – Dgstah Nov 23 '16 at 17:46
You could put it in a while loop, replacing NUMBEROFROWSDESIRED by a (shell) variable and iterating until the number of rows inserted is not a power of 2. (0 isn't a power of 2, hence if you need e.g. 16384 rows, you'll end up with an extra execution doing nothing). – Ronald Nov 24 '16 at 09:59

score 0 · Answer 2 · answered Nov 23 '16 at 18:11

I don't know anything about SQLYog but it looks like a MySQL tool, not SQL Server (this post was tagged with T-SQL). I also don't know much about MySQL but would suggest creating a permanent numbers table with as many numbers as you need then you can use it like this:

-- say I need the numbers 1 to 10:
SELECT N
FROM tally
WHERE N BETWEEN 1 AND 10; -- these can be variables

To create one you can use this syntax (which works in T-SQL or MYSql):

CREATE TABLE tally (N int NOT NULL,  PRIMARY KEY (N));

To populate it you can do so using a loop (I don't recommend loops but will make an exception here since no other syntax is working for you):

T-SQL version:

DECLARE @i int;
SET @i = 1;

-- T-SQL syntax
WHILE @i <= 1000 -- change this to the max number of rows that you want
BEGIN
  INSERT dbo.tally VALUES (@i);
  SET @i = @i+1;
END;

MySQL Syntax:

-- MySQL syntax
declare ii int unsigned default 1000;
declare i  int unsigned default 0;

truncate table foo;
start transaction;
while i < ii do
  insert into dbo.tally (N) values (i);
  set i=i+1;
end while;
commit;

Note: I can't test my MySQL query since I don't have access to a MySQL box at the moment.

score 0 · Answer 3 · edited Dec 09 '17 at 06:39

select (h*100+t*10+u+1) x from
(select 0 h 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) A,
(select 0 t 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) B,
(select 0 u 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) C
order by x;

This worked finally ! It seems pretty easy and straight forward and I can work with 1000 numbers as such.

In case there is a better way out there, please let me know.

How to generate a table in SQL having sequence numbers from 0 to a max value

3 Answers3