Categories
SQL / MySQL Tips and Tutorials

SQL: Randomly Shuffle Rows or Records – Reorder them in a random order

Easier than it appears. Just create a new table and import all those rows and records random selected and ordered by the RAND() SQL function:

CREATE TABLE new_table SELECT * FROM old_table ORDER BY RAND()

Of if you have created a table identical to the structure of the old one, use INSERT INTO instead:

INSERT INTO new_table SELECT * FROM old_table ORDER BY RAND()

That is of course if you want to preserve the primary key identification of each row, which is most likely what you want to do with old tables because of the legacy code and data entity relationships. However, if you want a grand new table with all the shuffled records completely rearranged in order as if it’s for a different application, you can ignore the primary key or ID by not importing the ID field of the old table.

For instance, you got ID, col1 and col2 in the old table as data fields. To create a grand new reordered or shuffled rows version of old table:

CREATE TABLE new_table SELECT col1, col2 FROM old_table ORDER BY RAND()

And a new primary key ID will be automatically assigned to each of the rows in the new table.

By Yang Yang

Hello, I'm Yang. I build online businesses that please people. Want to join in and post some useful articles on Kavoir.com? Shoot me a message.

3 replies on “SQL: Randomly Shuffle Rows or Records – Reorder them in a random order”

Does this work? If you say SELECT *, RAND() FROM tablename, you get the same RAND value for each record. So when ordering by RAND() you get no ordering at all because everything ties!

I just realized that you might not be using SQL Server SQL. For SQL Server, use NEWID() instead of RAND().

Comments are closed.