Getting rows in random order

I have a list of countries. The exact data structure is not important.

I want to retrieve a list of countries in Random order. There are plenty of good ways of jumbling data once it has been retrieved from a database, but it is possible to retrieve data in random order using only SQL. Well almost.

If my table were in an MS Access database, then I could make use of the RND function, which is actually a VB function (which is why I said "almost").

SELECT RND, Country.* FROM Country
ORDER BY RND

What's wrong with this query? The SQL optimizer, in its infinite wisdom, can see that I have not submitted any parameters to the RND function.

Therefore, the output of the RND function does not depend on which row is being selected.

Therefore, the RND function is called only once, at the beginning of the query, and the same value is output for every row.

Luckily, MS Access lets me do this:

SELECT RND(CountryID), Country.* FROM Country
ORDER BY RND(CountryID)

This query will output a list of countries in a different order every time, because the RND function will be called on every row.

The only caveat here, is that an application which uses this query, must initialize Visual Basic's random number generator with a call to RANDOMIZE prior to the first time this query is used.

OK, this would be handy if my table of countries was in Access, but it isn't.

SQL Server has a RAND function which is similar to but slightly different from Visual Basic's.

RAND takes a parameter which is used as a seed, (equivalent to a negative parameter for RND).

Consequently, the query

SELECT RAND(CountryID), Country.* FROM Country
ORDER BY RAND(CountryID)

will return records in the same order every time.

But it does give me some information which will help me trick the random number generator into giving me what I want.

For CountryID's which are numerically close to each other, the random numbers generated are also close to each other. But the last few digits of RAND(CountryID) look random enough to be useful.

It turns out that, at least for the small tables that I have tried this on, the following works pretty well:

SELECT RIGHT(convert(char(8),RAND(DatePart(ms,GetDate())+ID)),2),Country.*
FROM Country
ORDER BY RIGHT(convert(char(8),RAND(DatePart(ms,GetDate())+ID)),2)

This query will probably need some tweaking before I use it on a larger table (because I am only getting
two random digits) but it's a start. It also relies on the way SQL server's random number generator works and so may not be useful on another RDBMS.

Nevertheless, this technique is handy for generating test data. If I want to test a subset of the data in a database, I can change my query so that it reads

SELECT TOP 100
...

and put the results into a smaller 'test' database.

Adelle.