Tuesday, 13 August 2013

MSSQL - How to get rows in random sort order

Sometimes it is necessary to get the query result sorted in random order.

The first idea is to use a random number generator:
SELECT * FROM MyTable ORDER BY RAND();

But if we run this query several times, we can see that the rows are returned each time in the same order.
There is another function that returns something random - NEWID().
This function creates a unique value of type uniqueidentifier (GUID).
SELECT * FROM MyTable ORDER BY NEWID();

If you need to return a set of 100 random rows (Table Sample), you can do following:
SELECT TOP(100) * FROM MyTable ORDER BY NEWID();