CODEWITHSUNDEEP
mysqlcommon-table-expressionrecursive-querymysql-8.0
Jimmix
Jimmix

Reputation: 6536

MySQL 8 Recursive CTE generate number of rows

I would like to generate n count of random rows by using MySQL CTE. So far I have:

WITH RECURSIVE cte AS
(
    SELECT LEFT(MD5(RAND()),8) AS `three`
    UNION ALL
    SELECT LEFT(MD5(RAND()),8) AS `three`
    WHERE `rn` < 10
)
SELECT * FROM cte
;

How to make the condition 

WHERE `rn` < 10

working, assuming rn is a rows number returned by the recursive query?

I know that you may use: 

SELECT ROW_NUMBER() OVER () AS `rn`

to get the row number but how to use it? Is there any alternative solution?

I'm using MySQL 8.

Upvotes: 2

Views: 735

Answers (1)

sticky bit
sticky bit

Reputation: 37487

You can add a "control" column in the CTE, that counts from one up. Then you can limit the recursive step to end when the value in that column exceeds ten (or what ever n is). In the outer SELECT then just select the column with the random values, but not the "control" column.

WITH RECURSIVE cte
AS
(
SELECT left(md5(rand()), 8) three,
       1 i
UNION ALL
SELECT left(md5(rand()), 8) three,
       i + 1 i
       FROM cte
       WHERE i + 1 <= 10
)
SELECT three
       FROM cte;

db<>fiddle

Upvotes: 2

Related Questions