123

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

trejder
  • 17,148
  • 27
  • 124
  • 216
Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
  • I vote to reopen; the "duplicate" question is not specific to MS-SQL server, so you have to wade through a ton of info that is not relevant if you are looking for MS-SQL – Mark Sowul May 04 '20 at 14:48
  • @MarkSowul it's not specific to MS-SQL, it's applicable to *all* SQL types, which is what canonical duplicates are for. Further, "wade through a ton of info" is nonsense; the MS SQL solution is pinned to the top of the page in the *accepted answer* (which has the highest score by a factor of 20, if you care to sort answers that way). – TylerH May 04 '20 at 20:14
  • It's not "nonsense" (I thought we're supposed to be friendly here?). The 'accepted answer' is not one size fits all (look at the upvoted comments), and trying to find other alternatives involves lots of other answers that are not relevant if you are looking for a specific DBMS, especially since most of the answers don't even mention what DBMS they are for. – Mark Sowul May 06 '20 at 16:27

6 Answers6

204
SELECT * FROM table
ORDER BY NEWID()
Arsen Mkrtchyan
  • 49,896
  • 32
  • 148
  • 184
Dave Barker
  • 6,303
  • 2
  • 24
  • 25
  • 61
    For MySQL use `ORDER BY uuid()`. (...for those arriving on this page via a generic search, like me!) – Stan James Jan 07 '12 at 04:15
  • 3
    This works, but is insanely slow on the table I'm selecting from (14M+ rows). Is that to be expected? AFAIK, the table is properly indexed. – John Kraft Feb 15 '13 at 17:43
  • 4
    @John - You are selecting 14 million records and generating and ordering by a uniqueidentifier. Perhaps yuo're only after a single random row? In this case, do SELECT TOP 1 FROM table ORDER BY NEWID() – Dave Barker Feb 18 '13 at 05:37
  • @DaveBarker That's exactly what I'm after. It's for system integration testing. I'm selecting a random invoice id from our invoices table. I'm doing exactly as you suggested, but it is still ridiculously slow. The time is measured in minutes. – John Kraft Feb 18 '13 at 14:27
  • @John - Is your InvoiceId an integer and is it an identity column in incrementing order? – Dave Barker Feb 19 '13 at 00:32
  • @DaveBarker It is an integer. It is not an identity. It is the primary key (clustered, unique, primary key). – John Kraft Feb 19 '13 at 16:05
  • 5
    @John Kraft - If the numbers are incrementing and there aren't too many gaps in the number order then you could use the RAND function. Declare InvoiceId INT SELECT InvoiceId = RAND() * (SELECT MAX(InvoiceId) - MIN(InvoiceId) FROM table) PRINT InvoiceId SELECT * FROM table Where InvoiceId = InvoiceId Note: The InvoiceId should have the at sign but SO is unhappy with that – Dave Barker Feb 21 '13 at 23:12
  • @DaveBarker Seems to work exactly as I wanted, and it's extremely fast. Thanks for the help! – John Kraft Feb 22 '13 at 20:07
  • What do I do if I want to perform further manipulations of this data? i.e. if I'm randomizing it and then grabbing the top 10%, how do I then sort that 10%? order by is, sadly, invalid in subqueries in TSQL. – Mir Jul 30 '13 at 18:50
  • 2
    That does not work in MySQL. – Supreme Dolphin Jan 28 '16 at 16:20
  • 1
    For Postgres it's `ORDER BY random()`. – sk29910 Feb 09 '18 at 17:58
  • Might need a different solution within a stored proc or function. `"Invalid use of a side-effecting operator 'newid' within a function."` – Mark Jan 19 '21 at 04:41
28

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.

trejder
  • 17,148
  • 27
  • 124
  • 216
Alec Smart
  • 94,115
  • 39
  • 120
  • 184
  • 13
    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 Jul 13 '09 at 05:03
  • 2
    Hm, I'm pretty sure this has worked for me in the past, perhaps it depends on the product you use. – Ray Hidayat Jul 13 '09 at 05:04
  • It's don't seems that it works on MS SQL. – Arsen Mkrtchyan Jul 13 '09 at 05:09
  • 3
    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 Jul 13 '09 at 05:42
  • ...and the tag was "tsql": which implies MS SQL Server usually. – gbn Jul 13 '09 at 05:54
  • 8
    But yes, it would work on MySQL :) – KCD Nov 23 '11 at 20:50
  • 2
    the uuid() and newid() technique does not work for me on mysql, this answer did. – ricosrealm Feb 27 '13 at 22:57
  • This solution can't work on SQL Server. If you want this to work, you can create an `OUTER APPLY` with an subquery which generates the `RAND`. But the Solution with `NEWID()` is much better. – Ionic Jun 23 '15 at 07:09
  • I've tested it personally. This works only in MySQL, so it is a more valid solution than `NEWID()` which does not work in MySQL, that is in the case of MySQL. – Supreme Dolphin Jan 28 '16 at 16:19
  • `RAND` works fine with phpmyadmin...... – Karue Benson Karue Feb 19 '16 at 23:37
12

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

SELECT * FROM dbo.Foo ORDER BY NEWID();
devstuff
  • 8,277
  • 1
  • 27
  • 33
-1

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
  • 1,932
  • 13
  • 22
-1

Here's an example (source):

SET @randomId = Cast(((@maxValue + 1) - @minValue) * Rand() + @minValue AS tinyint);
trejder
  • 17,148
  • 27
  • 124
  • 216
PinoyDev
  • 949
  • 1
  • 10
  • 21
-2

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
Dan Both
  • 202
  • 2
  • 9
  • 5
    The question was tagged SQL Server. The RAND() doesn't work and the LIMIT isn't recognized, nor does the question ask to limit the results to one record. – LarsTech Apr 07 '16 at 22:23