Generate an integer sequence in MySQL with random order

Question

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

You are setting `@a` twice in the SQL, once in the `SET` and once in the `CROSS JOIN`.. — Raymond Nijland, Jul 18 '19 at 10:34
@RaymondNijland Yes that was my mistake..thank you for your reply but if I remove the cross join or one of them then also I can't get my expected output. But I got Solution of the derived table, working nicely for me. — Krishna Panchal, Jul 18 '19 at 15:11
@MadhurBhaiya answer `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 CROSS JOIN(SELECT @a:= 0)` with a CROSS JOIN should work.. — Raymond Nijland, Jul 18 '19 at 15:23

score 1 · Accepted Answer · answered Jul 18 '19 at 10:32

1

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

answered Jul 18 '19 at 10:32

Madhur Bhaiya

28,155
10
49
57

the outer SQL needs to have `ORDER BY @a` or `ORDER BY sequence_no` ... – Raymond Nijland Jul 18 '19 at 10:36
@RaymondNijland no I dont think so. Sequence number need to be in ascending order only (as per OP). He just needs the other records to be in random order.. – Madhur Bhaiya Jul 18 '19 at 10:37
*"no I dont think so"* SQL resultsets are by ANSI/ISO definition **orderless**, besides the topicstarter resultset seams to be sorted on the `sequence_no` on the expected output.. – Raymond Nijland Jul 18 '19 at 10:38
@RaymondNijland I know; but in this particular case, we are computing sequence number on-the-fly, so they are bound to be in the ascending order only. So far, in all the problems involving row number/rank etc using variables, I have never found the need for explicit sorting (again) at the outermost level. – Madhur Bhaiya Jul 18 '19 at 10:40
@RaymondNijland eventually the only solution is to upgrade to MySQL 8.0; and avoid using user variables :-) I did write an answer on this sometime back, trying to be as safe as possible when using variables (offcourse not 100%). You can check: https://stackoverflow.com/questions/53465111/determine-rank-based-on-multiple-columns-in-mysql/53465139#53465139 – Madhur Bhaiya Jul 18 '19 at 10:49
*"So far, in all the problems involving row number/rank etc using variables, I have never found the need for explicit sorting (again) at the outermost level. "* Yea most likely the MySQL Optimizer chooses to use the delivered table order as final "sort" to deliver fast results.. – Raymond Nijland Jul 18 '19 at 10:50

Generate an integer sequence in MySQL with random order

1 Answers1