Raymond writes "How would you suggest returning records from a query in a random order?" This is a little trickier than you might think. And the best solution involves a cursor. Yuck!
If your need of randomness is less strong than your laziness :)
You can use this :
select id, rand(@@IDLE % (@@ROWCOUNT + id)) as xx from foo order by xx
How it works :
First of all 'id' must be an integer.
@@IDLE : Returns the time in milliseconds SQL Serverâ„¢ has been idle since it was last started. Unless, your system is really stressed, you'll get a different value each time.
@@ROWCOUNT : Returns the number of rows affected by the last statement. As you can see, the more rows you've got the more 'RANDOMNESS' you get.
Returning Rows in Random Order (SQL 2000 on Win2000)
How about this:
use pubs go
select au_lname,newid() from authors order by newid() go
I believe the Windows api which implements newid() no longer returns sequential guids in Win 2000. I've tested this using SQL 2000 on Win 2000. Doesn't work using SQL7 on NT4. Haven't tried SQL7 on Win2000.
Another way to do this is to use the middle part of the random number that is returned. The following example uses about 6 digits starting from three in (exactly 6 from 3 if you use 1000 instead of 1024). Using RAND() is likely to be cheaper than using trigonometric functions.
SELECT ID, RAND(FLOOR( (RAND(ID)*1024-FLOOR(RAND(ID)*1024)) * 1048576) ) FROM TheTable ORDER BY 2
Article: Returning Rows in Random Order
Posted - 07/29/2000 : 18:13:24
