MySQL Sequence Number Generate as ROW

Question

I need a QUERY to generate sequence number as rows like:

SELECT n as seq_no FROM DUAL;

when n = 5, which returns result like :

OUTPUT:

+--------+
| seq_no |
+--------+
|  1     |
|  2     |
|  3     |
|  4     |
|  5     |
+--------+

So far I have tried:

SET @n = 10;
SET @i = 1;
SET @Q = '';

WHILE @i < @n DO
    SET @Q = @Q || 'UNION ALL SELECT' || @i || ' FROM DUAL';
    set @i= @i+1;
END WHILE;

SET @Q = SUBSTRING(@Q, 10); 

PREPARE stmt1 FROM @Q; 
EXECUTE stmt1; 
DEALLOCATE PREPARE stmt1;

But it is showing:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHILE @i < @n DO SET @Q = @Q || 'UNION ALL SELECT' || @i || ' FROM DUAL'' at line 1

Bernd Buffen · Accepted Answer · 2016-04-10 12:34:19Z

2

you can use this

SELECT @n:=(@n := @n +1) AS seq_no 
FROM someTable
CROSS JOIN (SELECT @n:= 0) AS parameter;

to use you old queries you can put it there:

SELECT @n:=(@n := @n +1) AS seq_no 
FROM ( SELECT ... OLD QUERY) AS q
CROSS JOIN (SELECT @n:= 0) AS parameter;

SAMPLE

MariaDB []> select * from abc;
+----+------+
| id | b    |
+----+------+
| 33 |  100 |
| 34 |  101 |
| 35 |   11 |
| 36 |   22 |
| 37 |   22 |
| 38 |   11 |
+----+------+
6 rows in set (0.00 sec)

MariaDB []> SELECT @n:=(@n := @n +1) AS seq_no  FROM ( SELECT * from abc) AS q CROSS JOIN (SELECT @n:= 0) AS parameter;
+--------+
| seq_no |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
+--------+
6 rows in set (0.00 sec)

MariaDB []>

To generate only a number from to you can use this:

SELECT 0 AS seq_no 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

In MariaDB is a sequence Engine there you can do all

MariaDB []> select seq from seq_1_to_10;
+-----+
| seq |
+-----+
|   1 |
|   2 |
|   3 |
|   4 |
|   5 |
|   6 |
|   7 |
|   8 |
|   9 |
|  10 |
+-----+
10 rows in set (0.04 sec)

MariaDB []>

The next try - at BETWEEN you can set the boundary

SELECT *
FROM (
  SELECT (d1.seq_no*10)+d2.seq_no AS seq_no
  FROM (
    SELECT 0 AS seq_no 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
    ) AS d1
 CROSS JOIN (
    SELECT 0 AS seq_no 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 
  ) AS d2
) AS result
WHERE seq_no BETWEEN 1 AND 13
ORDER BY seq_no;

SAMPLE

MariaDB []> SELECT *
    -> FROM (
    ->   SELECT (d1.seq_no*10)+d2.seq_no AS seq_no
    ->   FROM (
    ->     SELECT 0 AS seq_no 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
    ->     ) AS d1
    ->  CROSS JOIN (
    ->     SELECT 0 AS seq_no 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
    ->   ) AS d2
    -> ) AS result
    -> WHERE seq_no BETWEEN 1 AND 13
    -> ORDER BY seq_no;

+--------+
| seq_no |
+--------+
|      1 |
|      2 |
|      3 |
|      4 |
|      5 |
|      6 |
|      7 |
|      8 |
|      9 |
|     10 |
|     11 |
|     12 |
|     13 |
+--------+
13 rows in set (0.00 sec)

MariaDB []>

edited Apr 10, 2016 at 12:34

answered Apr 10, 2016 at 7:56

Bernd Buffen

15.1k2 gold badges27 silver badges41 bronze badges

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

5 Comments

splash58 Over a year ago

wrap your query in one more to get only requied amount of rows :

select * from (SELECT @n:=(@n := @n +1) AS seq_no  FROM sometable CROSS JOIN (SELECT @n:= 0) AS parameter) t where seq_no <= 5

muzahidbechara Over a year ago

could I replace someTable by which query will support in all database?

muzahidbechara Over a year ago

I have used DUAL because all database contains this table, but your query does not provide expected OUTPUT. giving error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'CROSS JOIN (SELECT @n:= 0) AS parameter' at line 3

muzahidbechara Over a year ago

@BerndBuffen , your query works fine when your abc table contains more rows than @n. Otherwise it's not working. For example, in your sample abc contains 6 rows; now how you get 1 - 10 as query result?

muzahidbechara Over a year ago

@BerndBuffen , Thanks for your last clue. By the concept you provided, I have tried to do few code and EDITED my question. But still it's not running. Could help me few?(and UPVOTE for last update :))

Collectives™ on Stack Overflow

MySQL Sequence Number Generate as ROW

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related