Tip of the day : Selecting Random Rows

A common feature request is to show a random selection of X rows from a table. The basic premise of how it's done is simple, but subtle inconsistencies mean that the syntax which works on MySQL won't work on SQL Server, and vice-versa. Here's how we've done it on each platform.

Basic Premise

In your query, as well as the columns you want to select, also select a calculated column which contains a random, non-sequential element, and sort on that column first.

MySQL

In MySQL v.4.1.2, the UUID() function was introduced. So why can't you select a UUID() and sort on that? Well, because although the UUID() function creates a unique identifier, it always seems to create them in a sequential order - the following query :

SELECT UUID(), name
FROM `help_keyword`
ORDER BY 1
LIMIT 5

produces these results on first run:

UUID()	name
ba109f04-064f-1029-82dd-fe32006a1320	<>
ba109f35-064f-1029-82dd-fe32006a1320	ACTION
ba109f4b-064f-1029-82dd-fe32006a1320	ADD
ba109f5e-064f-1029-82dd-fe32006a1320	AES_ENCRYPT
ba109f73-064f-1029-82dd-fe32006a1320	AFTER

and these results on the next run:

UUID()	name
5d7f49d4-0650-1029-82dd-fe32006a1320	<>
5d7f4a08-0650-1029-82dd-fe32006a1320	ACTION
5d7f4a1f-0650-1029-82dd-fe32006a1320	ADD
5d7f4a33-0650-1029-82dd-fe32006a1320	AES_ENCRYPT
5d7f4a48-0650-1029-82dd-fe32006a1320	AFTER

so you can see that although each UUID is unique, they're always created sequentially, which means it's no use to use in our random selection.

The solution is to use a random number, created via the RAND() function. Using this query:

SELECT RAND(), name
FROM `help_keyword`
ORDER BY 1
LIMIT 5


we get these results:

uid	name
0.00856768061273515	OUTFILE
0.0149379475181344	COMPACT
0.0210173549326298	RENAME
0.0219931677188661	LAST
0.0236370014246898	MOD

on the first run, and on the second, we get :

uid	name
0.00319886301010741	MASTER_PORT
0.00735198055147378	PACK_KEYS
0.00786910020547835	DISTINCT
0.00797068235275399	BIGINT
0.0113145066530579	LINESTRINGFROMWKB

So that's how a random selection can be made on MySQL.

SQL Server

On SQL Server, there is also a random number generator, also called RAND() :

select TOP 5
RAND() AS randomNumber,
tblUser.nvcSCreenName
FROM tblUser
ORDER BY 1

but this produces, shall we say, unexpected results:

randomNumber	nvcScreenName
0.33363907482884941	Al
0.33363907482884941	AlanPartridge
0.33363907482884941	AlD
0.33363907482884941	Ali dahodza
0.33363907482884941	Andy

In SQL Server, the random number generator only gets seeded once per query, not once per row - which makes it of no use for our purposes.

The solution is to create a UUID instead - but on SQL Server the funciton you will need is NEWID():

select TOP 5
NEWID(),
tblUser.nvcSCreenName
FROM tblUser
ORDER BY 1

which gives us these results on first run:

randomNumber	nvcScreenName
8B7CC32F-20EC-4111-86DE-17B4183B4C94	Al
DCF6B780-55B5-4C2D-A7FA-27D9C8C44F33	neil
B5C5F469-ADC2-4866-99F0-297355DFBF22	AlanPartridge
C7DBE10F-94A0-420E-8894-33511DE46C1D	dave10
A3360F90-9B0F-4C4F-9438-3B6F58BF5670	Ruairí

and these results on second run:

randomNumber	nvcScreenName
C27A9E7F-9D51-45A3-8661-0FABFA511617	dave3
BF9AB329-F863-4330-BBBC-105F1C19717F	AlanPartridge
D45DE252-FF96-4D44-8D76-116981211A93	Ruairí
D8EAA1B7-9558-41CF-B844-14E5E792CCA9	dave5
9A34C260-10A9-40E6-B020-156737A11C43	Al

So bingo, bob's yer firkin, job's a good'un, etc etc - that's how it can be achieved on SQL Server.