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:

view plain print about
1SELECT NEWID()
2
3Example Output: 0B7869E5-1428-4CA9-9D5A-CDA0E9756DC2

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

view plain print about
1SELECT RAND();
2
3Example Output: 0.93845168309142

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

SQL Server

view plain print about
1SELECT TOP 1 *
2FROM mytable
3ORDER BY NEWID()

MySQL

view plain print about
1SELECT *
2FROM mytable
3ORDER BY RAND()
4LIMIT 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.