views:

613

answers:

4

Hi
Is it possible to write SQL query that returns table rows in random order every time the query run?

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
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
Hm, I'm pretty sure this has worked for me in the past, perhaps it depends on the product you use.
Ray Hidayat
It's don't seems that it works on MS SQL.
ArsenMkrt
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
...and the tag was "tsql": which implies MS SQL Server usually.
gbn
+5  A: 

SELECT * FROM table ORDER BY NEWID()

Dave Barker
+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
The obvious choice, the RAND() function doesn't work directly, but here's a workaround: http://weblogs.sqlteam.com/jeffs/archive/2004/11/22/2927.aspx
devstuff
+1 it works, but Dave Barker was faster :)
ArsenMkrt
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