CODEWITHSUNDEEP
sqlsql-serverrandom
Ruba Sbeih
Ruba Sbeih

Reputation: 27

How to shuffle rows in random order in SQL and save results in new table?

So I have a table called Customers 

                     id  | name
                    ------------
                     1     Tom
                     2     Bob
                     3     Sam
                     4     Angel
                     5     Joe

I want to select the rows in the 'name' column in a random order, and insert those rows into a new table, I tried this 

       select Name into TestTable from Customer order by NEWID() 
       select * from TestTable

But it doesn't work? The rows in TestTable are in the same order as they were in the original table.

                         Tom
                         Bob
                         Sam
                         Angel
                         Joe

I tried executing multiple times but the result is the same every time. It seems that the rows are being inserted into the new table before the randomization happens. Any ideas?

Upvotes: 0

Views: 2097

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271201

Rows in a table have no ordering. They represent unordered sets. If you want rows in a random order, then order when you extract them:

select name
from testtable
order by newid();

Alternatively, you can add an identity column, which should preserve the ordering for the group by:

select identity(int) as id, Name
into TestTable
from Customer order by NEWID();

select *
from TestTable
order by id;

Here is a db<>fiddle.

Note: I am actually surprised that your example doesn't work on a small set of data. I might guess that is an optimization implemented by SQL Server to avoid sorting the data because it knows that tables are unordered. The identity() function however is documented to preserve ordering.

Upvotes: 1

Related Questions