Fetch random set of rows through SQL Query
Posted by Virendra Dugar on April 27, 2009
If you need to fetch random rows from a SQL server table then RAND() function will do the job for you.
select rand(), productid, productname from product
The problem with this method is that the Rand() function returns the same value for all rows within a given call, See image below.
You can also supply a seed value to the rand function to generate a unique value.
select top 5 rand(productid) as val ,productid, productname from product order by val
No matter, how many times you run this command, you will always get top 5 rows even though unique random is generated every time.
In such cases how to fetch random rows? SQL Server provides one more mechanism to access random rows. NewID(), which returns a GUID (Globally Unique Identifier).
SELECT TOP <n>
ORDER BY NEWID()
select top 5 newid() as val, productid, productname from Product order by val
The key is the use of NEWID() function that returns a GUID. Results will differ, since NewID() manufactures a GUID each time it is called. Therefore, if you run the above command two or three times, you will get different result sets.