Technical Article

Randomise the order of a SELECT

,

I have a set of data that I need to display in a random order each time I displayed it. There are several ways to do this, for example using a CURSOR to add each record to a temp table with a GUID, but a quick and dirty way to do this is to use the RAND function.

When you put RAND into a SELECT it returns the same value for each row - not very useful in this case! To produce a random set of data I added a seed based on an IDENTITY column with an additional seed value (values arround the 10,000 mark work best). The random value is Modulo to 1000, because the higher parts of the RAND value aren't that random. The data part of the RAND is based on the code in the RAND entry in BOL. If someone has a better understanding of the RAND funciton they may be able to simplify this statement.

I haven't compared performance with the CURSOR method, but on small tables it seems works well. You can also check the results by averaging the random value, which should return roughly 500.

CREATE PROCEDURE [dbo].[spRandomiseOrder] 
@nSeedint
AS

   SELECT MyTable.MyData
   FROM   MyTable
   ORDER BY (CONVERT(int, 
      RAND(((MyTable.MyId + @nSeed) % 100) 
      * DATEPART(ms, GETDATE())) * 100000) % 1000)

GO

Rate

Share

Share

Rate