Finding a Random Row

Every now and then I get a requirement to do something to a random table record. The most common is to retrieve a random record for display. Other times it is part of a data scrambling routine. So how is this done?

Most database platforms have a function that generates a unique identifier. SQL Server has NEWID(), which generates a GUID:

SELECT NEWID()

Example Output: 0B7869E5-1428-4CA9-9D5A-CDA0E9756DC2

MySQL has RAND(), which generates a random floating-point value:

SELECT RAND();

Example Output: 0.93845168309142

Using these functions, retrieving a random row from a table becomes as simple as:

SQL Server

SELECT TOP 1 *
FROM mytable
ORDER BY NEWID()

MySQL

SELECT *
FROM mytable
ORDER BY RAND()
LIMIT 1;

So what is really going on behind the scenes? The key to the randomness is the ORDER BY clause. As each row in the table is evaluated, it is assigned a unique value via the NEWID() or RAND() function. The value changes for each row and each time the query is executed.

Comments (1) Print Posted At : September 30, 2008 1:20 PM 455 Views

Comments

Tracy Xia's Gravatar Is it possible to be random AND not at the same time? For example, what if you wanted to get 500 random people out of the PEOPLE table, and also get the number people evenly spread out across all 50 states? This can be done easily if you use a bunch of SELECT and UNION statements where you just get 50 random people out of each state. But, that is 50 SELECT statements! :-) Is there a more clever way to do that?
# Posted By Tracy Xia | 10/18/08 11:42 AM