Clever Geek Handbook

How to randomize the order of data in 3 columns

I have 3 columns of data in SQL Server 2005:

LASTNAME NAME CITY

I want to randomly reorder these 3 columns (and change the data) so that the data is no longer meaningful. Is there an easy way to do this? I don't want to change any data, I just want to order the index randomly.

+2


source to share


4 answers


When you say "reorder" these columns, do you mean that you want some of the last names to end up in the first named column? Or do you want to say that you want some of the latter names to be associated with a different name and city?

I suspect you mean the latter, in which case you might find a programmatic solution easier (as opposed to a straight SQL solution). Sticking with SQL, you can do something like:

UPDATE the_table
SET lastname = (SELECT lastname FROM the_table ORDER BY RAND())

Depending on which RDBMS you are using, this might only work for one line, it might cause all the last names to be the same, or it might require some syntax changes to work at all, but the basic approach will be right. Of course, some testing on a copy of the table is warranted before trying it on the real thing.



Of course, so that the early names and cities are also randomly reordered, you can apply a similar query to any of these columns. (Applying it to all three doesn't make much sense, but it doesn't hurt either.)

Since you don't want to change the original data, you can do this in a temporary table filled with all the rows.

Finally, if you just want one random value from each column, you can do it locally, without making a copy of the data, with three separate queries: pick a random first name, one random last name, and the last random phone number.

+2


source


I suggest using newid with a checksum to do the randomization



 SELECT LASTNAME, FIRSTNAME, CITY FROM table ORDER BY CHECKSUM(NEWID())
0


source


In SQL Server 2005+, you can prepare a ranked rowset containing three target columns and three additional calculated columns filled with random ranking (one for each of the three target columns). Then the ranked rowset will be concatenated to itself three times using the ranked columns, and finally each of the three target columns will be inferred from its own instance of the ranked rowset. Here's an illustration:

WITH sampledata (FirstName, LastName, CityName) AS (
  SELECT 'John', 'Doe', 'Chicago' UNION ALL
  SELECT 'James', 'Foe', 'Austin' UNION ALL
  SELECT 'Django', 'Fan', 'Portland'
),
ranked AS (
  SELECT
    *,
    FirstNameRank = ROW_NUMBER() OVER (ORDER BY NEWID()),
    LastNameRank  = ROW_NUMBER() OVER (ORDER BY NEWID()),
    CityNameRank  = ROW_NUMBER() OVER (ORDER BY NEWID())
  FROM sampledata
)
SELECT
  fnr.FirstName,
  lnr.LastName,
  cnr.CityName
FROM ranked fnr
  INNER JOIN ranked lnr ON fnr.FirstNameRank = lnr.LastNameRank
  INNER JOIN ranked cnr ON fnr.FirstNameRank = cnr.CityNameRank

This is the result:

FirstName LastName CityName
--------- -------- --------
James     Fan      Chicago
John      Doe      Portland
Django    Foe      Austin
0


source 


select *, rand() from table order by rand();

I understand that some versions of SQL have rand () which does not change for every row. Check yours. Powered by MySQL.

-1


source






More articles:


All Articles