CODEWITHSUNDEEP
mysqlrandom
Smokey McTokes
Smokey McTokes

Reputation:

MySQL query to assign a unique random number to each row

I wish to attach a column to my table which will be a random number from a sequential list = to the number of rows.

So, if my table had 999 rows, then the numbers 1 to 999 would be assigned randomly and uniquely.

Now, I figured that I could add a dummy TempRandomColumn=Rand(), sort by that and add the numbers sequentially using PHP. But that means 999 MySQL statements.

Is there a way to do this using a single MySQL statement?

Thanks for any pointers.

Upvotes: 12

Views: 9067

Answers (2)

vartec
vartec

Reputation: 134601

SET @i=1;
SELECT t.*, @i:=@i+1 as RAND_NUM FROM your_table t ORDER BY RAND();

Upvotes: 1

Quassnoi
Quassnoi

Reputation: 425371

SET @r := 0;
UPDATE  items2
SET     author_id = (@r := @r + 1)
ORDER BY
        RAND()

Upvotes: 26

Related Questions