Is it possible to write SQL query that returns table rows in random order every time the query run?
This question is related to
sql
sql-server
tsql
The usual method is to use the NEWID() function, which generates a unique GUID. So,
SELECT * FROM dbo.Foo ORDER BY NEWID();
This is the simplest solution:
SELECT quote FROM quotes ORDER BY RAND()
Although it is not the most efficient. This one is a better solution.
Here's an example (source):
SET @randomId = Cast(((@maxValue + 1) - @minValue) * Rand() + @minValue AS tinyint);
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.
SQL Server / MS Access Syntax:
SELECT TOP 1 * FROM table_name ORDER BY RAND()
MySQL Syntax:
SELECT * FROM table_name ORDER BY RAND() LIMIT 1
Source: Stackoverflow.com