SQL Server Random Sort

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) ;

sql-server

select foo from Bar order by newid()

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.

CodeHunter