Return rows in random order

user_87053 0
Viewed 153056

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

tsql sql-server sql
6 Answers
SELECT * FROM table
ORDER BY NEWID()
user_644340

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.

user_4269960

The usual method is to use the NEWID() function, which generates a unique GUID. So,

SELECT * FROM dbo.Foo ORDER BY NEWID();
devstuff_413210

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_190970
Related