CODEWITHSUNDEEP
mysql
Krishna Panchal
Krishna Panchal

Reputation: 23

Generate an integer sequence in MySQL with random order

Is there any other way to generate sequence_no with order by random in MYSQL??? I have a table: captcha

SET @a:= 1;
SELECT @a:=@a+1 as sequence_no,captcha_id, captcha_name
FROM captcha,(SELECT @a:= 0) AS a
ORDER BY RAND()

my expected output :

sequence_no | captcha_id | captcha_name 
----------------------------------------
01          | 11         | name1
02          | 06         | name2

my query output : 

sequence_no | captcha_id | captcha_name
---------------------------------------- 
11           | 11          | name1
06           | 06          | name2

Upvotes: 1

Views: 129

Answers (1)

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

To achieve this, you will first need to perform explicit random-sorting inside a Derived Table. Then, use the subquery result, to set a sequence number (for the random sorted rows):

SET @a:= 0;
SELECT @a:=@a+1 as sequence_no,
       dt.captcha_id, 
       dt.captcha_name 
FROM (
       SELECT captcha_id, captcha_name 
       FROM captcha 
       ORDER BY RAND()
     ) AS dt

Upvotes: 2

Related Questions