Menu
What is the best way to sort the results of a sql query into a random order within a stored procedure?
569
Using the unseeded RAND() function as a column in the SELECT statement returns the same value on every row as shown in the SortOrder column. To solve this problem, we need to pass a changing seed value to the RAND() function for every row to ensure that new random numbers are generated for each row.
SQL Server NewId() generates a random GUID or unique identifier which can be used to return randomized rows from a SELECT query. T-SQL developers will realize that the return list of a SQL SELECT query is sorted randomly when they place "NEWID() function in the "ORDER BY" clause of the SELECT statement.
Using NEWID in a CREATE TABLE statement. Applies to: SQL Server. The following example creates the cust table with a uniqueidentifier data type, and uses NEWID to fill the table with a default value. In assigning the default value of NEWID() , each new and existing row has a unique value for the CustomerID column.
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) ; 
select foo from Bar order by newid() If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
