Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2001-03-15 : 00:21:00
|
Dave writes "Here's a way to solve the problem without using a cursor. This would only be efficient if you have to regularly return recordsets in random order, 'cuz setup's fairly expensive.
Almost every stats textbox has a table of random numbers in the back for use in various exercises throughout the book. This solution implements a similar strategy.
Create a two-column permanent table (RandomNumber) with RandomID as an identity column (starting with 0 and incrementing up to 9999) and RandomFloat. Put 10,000 records in this table with random numbers in RandomFloat. You'll have to use a cursor or a loop to do this - but you'll only have to do it once.
Then, for each query you want returned randomly, join in RandomNumber like this:
set @intOffset=RAND() * 10000
SELECT YourTable.YourID, yada
FROM YourTable, RandomNumber
WHERE (YourTable.YourID + @intOffset) % 10000 =
RandomNumber.RandomID
ORDER BY RandomNumber.RandomFloat
Using "@intOffset" and "% 10000" will give you a new place in the list to start each time you use it, so you'll be getting different records joining each time you use it.
Also, if you want to repeat the same random recordset, just assign @intOffset explicitly.
Downsides:
YourTable has to have an identity column - although it can have gaps in it.
It still works if YourTable has > 10,000 rows, but the first and 10,001st records will be assigned the same random number and always be next to each other in the returned recordset. Two solutions to that: put more rows in RandomNumber OR (and here's where I could use some feedback) figure out how to use a different @intOffset with each batch of 10,000 YourTable rows." |
|
|
|
|
|
|
|
Returning Rows in Random Order - Part IV