CODEWITHSUNDEEP
mysql
muzahidbechara
muzahidbechara

Reputation: 253

MySQL Sequence Number Generate as ROW

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

Upvotes: 0

Views: 1956

Answers (1)

Bernd Buffen
Bernd Buffen

Reputation: 15057

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 []>

Upvotes: 2

Related Questions