Problem solve Get help with specific problems with your technologies, process and projects.

Simpler way to get a randomly sorted column

Code to give a randomly sorted column without altering a table.

I think this code is giving me as close to a randomly sorted column as the last tip about random number sorts in a simpler fashion. Probably better idea to use a cursor for the loop, but I have avoided altering a table and copying and identity column as in the last tip. It works here. What do you think?

SELECT @customercount = (select count(*) from table_customer);
SELECT @lcount = 1;

CREATE TABLE #random_pick
  (rownum integer identity(1,1),
   randnum decimal(8,4),
   customer_id integer,
   lastname varchar(30),
   firstname varchar(20),
   hphone varchar(13),
   email varchar(45));

INSERT INTO #random_pick 
SELECT 0, * FROM table_customer;

WHILE @lcount <= @customercount
  BEGIN
     UPDATE #random_pick 
     SET randnum = rand() 
     WHERE rownum = @lcount;
     SET @lcount = @lcount + 1
  END

SELECT * FROM #random_pick ORDER BY randnum;


This was last published in May 2002

Dig Deeper on SQL Server Database Modeling and Design

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchBusinessAnalytics

SearchDataCenter

SearchDataManagement

SearchAWS

SearchOracle

SearchContentManagement

SearchWindowsServer

Close