Hi
Is it possible to write SQL query that returns table rows in random order every time the query run?
views:
613answers:
4
A:
Although not the most efficient:
SELECT quote FROM quotes ORDER BY RAND()
This is a better solution http://www.titov.net/2005/09/21/do-not-use-order-by-rand-or-how-to-get-random-rows-from-table/
Alec Smart
2009-07-13 05:00:52
Pretty sure that doesn't work. The RAND() in the ORDER BY clause is only calculated once, so you end up with a natural order. To test it, try ORDER BY RAND(), 1 and you'll get a list of your quotes orderd by the first column (at least in SQL Server 2005).
Matt Hamilton
2009-07-13 05:03:22
Hm, I'm pretty sure this has worked for me in the past, perhaps it depends on the product you use.
Ray Hidayat
2009-07-13 05:04:37
It's don't seems that it works on MS SQL.
ArsenMkrt
2009-07-13 05:09:00
This does not work and I guess you did not try this either. RAND() is fixed for the duration of the SELECT on MS SQL Server. You have to use NEWID()
gbn
2009-07-13 05:42:53
...and the tag was "tsql": which implies MS SQL Server usually.
gbn
2009-07-13 05:54:59
+1
A:
The usual method is to use the NEWID() function, which generates a unique GUID. So,
SELECT * FROM dbo.Foo ORDER BY NEWID();
devstuff
2009-07-13 05:09:10
A:
To be efficient, and random, it might be best to have two different queries.
Something like...
SELECT table_id FROM table
Then, in your chosen language, pick a random id, then pull that row's data.
SELECT * FROM table WHERE table_id = $rand_id
But that's not really a good idea if you're expecting to have lots of rows in the table. It would be better if you put some kind of limit on what you randomly select from. For publications, maybe randomly pick from only items posted within the last year.
nilamo
2009-07-13 05:10:30