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
– Raymond Nijland, Commented Jul 18, 2019 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
– Krishna Panchal, Commented Jul 18, 2019 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
– Raymond Nijland, Commented Jul 18, 2019 at 15:23

Madhur Bhaiya · Accepted Answer · 2019-07-18 10:32:45Z

2

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, 2019 at 10:32

Madhur Bhaiya

28.9k10 gold badges53 silver badges61 bronze badges

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

6 Comments

Raymond Nijland Over a year ago

the outer SQL needs to have ORDER BY @a or ORDER BY sequence_no ...

Madhur Bhaiya Over a year ago

@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..

Raymond Nijland Over a year ago

"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..

Madhur Bhaiya Over a year ago

@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 Over a year ago

@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: stackoverflow.com/questions/53465111/…

|

Collectives™ on Stack Overflow

Generate an integer sequence in MySQL with random order

1 Answer 1

6 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related