CODEWITHSUNDEEP
sqlt-sqlrandomsql-server-2012
Jimbo
Jimbo

Reputation: 23014

T-SQL Randomize order of results using RAND(seed)

Im using the following statement (this is a shortened version as an example) to get results from my Microsoft SQL Express 2012 database:

SELECT id, name, city 
FROM tblContact 
ORDER BY RAND(xxx)

and injecting a seed stored in the session for the xxx part so that the results are consistently random for a given session (so when paging through results, the user doesn't see duplicates)

PROBLEM: No matter what the seed is, the results get returned in the same order

I have also tried this:

SELECT id, name, city, RAND(xxx) AS OrderValue 
FROM tblContact 
ORDER BY OrderValue

Both give the same (unexpected result) - am I using this incorrectly?

Upvotes: 1

Views: 2550

Answers (3)

Dimt
Dimt

Reputation: 2328

The value of rand(seed) will be the same for the entire query, You my want to use the ID column to generate random value on the row per row basis:

SELECT id, name, city, RAND(xxx + id) AS OrderValue 
  FROM tblContact ORDER BY OrderValue

However I've been developing some functionality in the past where I needed to have random order for different session, but the same order within the same session. At that time I have used HASHBYTES() and it worked very well:

 SELECT id, name, city, HASHBYTES('md5',cast(xxx+id as varchar)) AS OrderValue 
   FROM tblContact ORDER BY OrderValue

Upvotes: 7

Gordon Linoff
Gordon Linoff

Reputation: 1271171

Often, the newid() function is used for this purpose:

SELECT id, name, city
FROM tblContact
ORDER BY newid();

I have heard that rand(checksum(newid())) actually has better properties as a random number generator:

SELECT id, name, city
FROM tblContact
ORDER BY rand(checksum(newid()));

If you want consistent result from one query to the next, then @dimt's solution using id or a function of id.

Upvotes: 1

Jaaz Cole
Jaaz Cole

Reputation: 3180

In SQL Server, Rand() is calculated once for the query. To get a random order, use ORDER BY NEWID().

Upvotes: 1

Related Questions