Returning a result set
in random order
Baya Pavliashvili
22 May 2002, Rating --- (out of 5)
At times, you
might need to return the SQL Server data to the client in a random order. For
instance, in educational environment you might want to randomize the order of
questions and answers on a test, so that students can't easily cheat by sharing
the answer IDs. Similarly, if you want to return a different image each time
someone visits your website you can run a "SELECT TOP 1" query on the
list of your image files and order them randomly.
Unfortunately
SQL Server doesn't provide built-in functionality for random sorting of the
result set. You can use the ORDER BY clause to sort the results by different
columns returned in ascending or descending order. However, that doesn't really
give you truly random results. The good news is that a bit of coding can help
you get around this limitation. Here is how you can do that.
SQL Server does
have a built-in function that returns a random number, which can be executed as
follows:
SELECT RAND()
Result:
0.66278421915814423
You can specify
a seed for the RAND() function within the parentheses, but executing the
function this way returns the same number every time for the same seed. For
instance, the random of 2 will always be 0.7136106261841817.
To demonstrate
returning a random result set, I'll use the 'authors' table in the 'pubs'
database and return the list of author names randomly. I first get a list of
all authors into a temporary table, as follows:
CREATE TABLE #temp ( au_lname VARCHAR(30) NOT NULL, au_fname VARCHAR(30) NOT NULL, sort_column INT NULL) INSERT #temp ( au_lname, au_fname ) SELECT DISTINCT au_lname, au_fname FROM authors
If you check
the list of authors in the temporary table at this point it is ordered
alphabetically. That is because I used the DISTINCT keyword, which automatically
orders the results of a SELECT. There are only two ways I can sort on the name,
so I need something else--I'll use the sort_column column to order the result
set.
Next, I will
add an identity column to the table, to get a simple enumeration. As you might
be aware the ALTER TABLE syntax supports adding an identity column to the
table. The syntax expects the seed and increment values for the IDENTITY
property, defaulting both values to 1. The trick is to start the identity
column with a different seed (or a different increment) for each execution of
the script. So I use the RAND function (without specifying the randomization
seed) to get the random identity seed:
DECLARE @i INT, @sql VARCHAR(300) -- get a random seed for the -- IDENTITY columnSET @i = ROUND(RAND() * 10, 0) SELECT @sql = 'ALTER TABLE #temp ADD dummy_identity INT IDENTITY(' SELECT @sql = @SQL + CAST(@i AS VARCHAR) + ', 1)'EXEC(@sql)
Ordering my
result set on the identity column will not provide the functionality I need,
even though the identity seed will be different for each run the result set
will always be sorted the same way. Therefore, I need to modify identity values
randomly. SQL Server won't let me explicitly update the identity column values;
therefore, I'll copy dummy_identity values into the existing sort_column
column:
SELECT @sql = ' ' SELECT @sql = @sql + ' UPDATE #temp SET sort_column = dummy_identity 'EXEC(@sql)
Notice that I
had to reinitialize @sql variable. Since I'm using the EXEC syntax the scope of
execution of the dynamic SQL statement is different from the rest of the batch.
By the way, sp_executesql behaves the same way. Had I tried executing the
script without reinitializing the variable I would have received a runtime
error informing me that dummy_identity column does not exist.
Next, I'll
update the sort_column column randomly by running through a cursor on a
temporary table:
DECLARE @sort_column INTDECLARE temp_cursor CURSOR FORSELECT sort_column FROM #tempOPEN temp_cursorFETCH NEXT FROM temp_cursor INTO @sort_columnWHILE @@FETCH_STATUS = 0BEGIN