SQL Server Random Sort
Sql ServerSql Server Problem Overview
What is the best way to sort the results of a sql query into a random order within a stored procedure?
Sql Server Solutions
Solution 1 - Sql Server
This is a duplicate of SO# 19412. Here's the answer I gave there:
select top 1 * from mytable order by newid()
In SQL Server 2005 and up, you can use TABLESAMPLE to get a random sample that's repeatable:
SELECT FirstName, LastName FROM Contact TABLESAMPLE (1 ROWS) ;
Solution 2 - Sql Server
select foo from Bar order by newid()
Solution 3 - Sql Server
Or use the following query, which returns a better random sample result:
SELECT * FROM a_table WHERE 0.01 >= CAST(CHECKSUM(NEWID(), a_column) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)
0.01 means ~1 percent of total rows.
Quote from SQL 2008 Books Online: > If you really want a random sample of > individual rows, modify your query to > filter out rows randomly, instead of > using TABLESAMPLE.
Solution 4 - Sql Server
You can't just ORDER BY RAND(), as you know, because it will only generate one value. So use a key for a seed value.
SELECT RAND(object_id), object_id, name FROM sys.objects ORDER BY 1