CODEWITHSUNDEEP
sqlpostgresql
Robinson
Robinson

Reputation: 45

How to make rows shuffle?

There is a table with over 10+ rows, and now needed to shuffle all rows randomly and create a new table on it. any ideas ?

Using select * from table order by random() seems slow. raw table is like,and the target column is separated into two parts:

+--------+------+--------+------+-----+--------+
| cst_id | name | salary | fund | age | target |
+--------+------+--------+------+-----+--------+
|      1 | a    |    100 | Y    |  33 |      0 |
|      2 | b    |    200 | Y    |  21 |      0 |
|      3 | c    |    300 | Y    |  45 |      0 |
|      4 | d    |    400 | N    |  26 |      0 |
|      5 | e    |    500 | N    |  37 |      0 |
|      6 | f    |    600 | Y    |  56 |      0 |
|      7 | g    |    700 | Y    |  44 |      0 |
|      8 | h    |    800 | N    |  22 |      1 |
|      9 | i    |    900 | N    |  38 |      1 |
|     10 | j    |   1000 | Y    |  61 |      1 |
|     11 | k    |   1100 | N    |  51 |      1 |
|     12 | l    |   1200 | N    |  21 |      1 |
|     13 | m    |   1300 | Y    |  32 |      1 |
|     14 | n    |   1400 | N    |  17 |      1 |
+--------+------+--------+------+-----+--------+

after:
+--------+------+--------+------+-----+--------+
| cst_id | name | salary | fund | age | target |
+--------+------+--------+------+-----+--------+
|      1 | a    |    100 | Y    |  33 |      0 |
|      2 | b    |    200 | Y    |  21 |      0 |
|      8 | h    |    800 | N    |  22 |      1 |
|      9 | i    |    900 | N    |  38 |      1 |
|      3 | c    |    300 | Y    |  45 |      0 |
|     13 | m    |   1300 | Y    |  32 |      1 |
|     14 | n    |   1400 | N    |  17 |      1 |
|      5 | e    |    500 | N    |  37 |      0 |
|      6 | f    |    600 | Y    |  56 |      0 |
|      7 | g    |    700 | Y    |  44 |      0 |
|     10 | j    |   1000 | Y    |  61 |      1 |
|     11 | k    |   1100 | N    |  51 |      1 |
|      4 | d    |    400 | N    |  26 |      0 |
+--------+------+--------+------+-----+--------+

Upvotes: 0

Views: 2935

Answers (1)

Chaitrali Patne
Chaitrali Patne

Reputation: 133

Following explanation is to create NEW table from existing one with same data as in old one(same schema) with shuffled rows.

Create a new table and import all those rows and records from first table, randomly selected and ordered by the RAND() SQL function:

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

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

But in SQL, Relations have no order. Rows in a relational database are not sorted. You may get different order while retrieving.

Upvotes: 1

Related Questions