57

What is the best way to sort the results of a sql query into a random order within a stored procedure?

Martynnw
  • 10,625
  • 5
  • 28
  • 27
  • 2
    possible duplicate of [How to request a random row in SQL?](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) – exhuma Aug 11 '14 at 13:39

4 Answers4

86

This is a duplicate of SO# 19412. Here's the answer I gave there:

select top 1 * from mytable order by newid()

In SQL Server 2005 and up, you can use TABLESAMPLE to get a random sample that's repeatable:

SELECT FirstName, LastName FROM Contact TABLESAMPLE (1 ROWS) ;
Community
  • 1
  • 1
Jon Galloway
  • 52,327
  • 25
  • 125
  • 193
  • Though this is not the best answer according to the other question linked to: http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql – Doug S Nov 08 '12 at 20:49
  • 3
    This question is not an exact duplicate of SO#19412 which asks for a random row from a query, even though this is marked as the answer, as this only gives 1 row not the results of the query sorted randomly. – Chad Miller Sep 11 '15 at 17:09
  • The TABLESAMPLE tip is gold! – Kong Jun 15 '16 at 05:39
  • 4
    In fact, this doesn't answer the question *as asked* at all. Since the asker has marked this as the best answer, it looks like he was having trouble articulating his actual question, but that doesn't change the fact that the question posed is "how do I randomly sort **all** of the rows returned by my query", while this answer says "here's how to return a single random row from a table". – Martha Aug 22 '18 at 15:24
  • TABLESAMPLE only works for a local table. For a general query you'll have to rely upon another construct. – Fabian Pijcke Apr 17 '20 at 14:12
21
select foo from Bar order by newid()
Jimmy
  • 89,068
  • 17
  • 119
  • 137
8

Or use the following query, which returns a better random sample result:

SELECT * FROM a_table WHERE 0.01 >= CAST(CHECKSUM(NEWID(), a_column) & 0x7fffffff AS float) / CAST (0x7fffffff AS int)

0.01 means ~1 percent of total rows.

Quote from SQL 2008 Books Online:

If you really want a random sample of individual rows, modify your query to filter out rows randomly, instead of using TABLESAMPLE.

endo64
  • 2,269
  • 2
  • 27
  • 34
  • 3
    This runs slower than TABLESAMPLE and faster than ORDER BY NEWID() on a big table. – endo64 Oct 15 '10 at 13:13
  • As I was looking for a random sampling from the results of a query this particular solution gave me what I was looking for although harpo's answer would order the results randomly. – Chad Miller Sep 11 '15 at 17:15
4

You can't just ORDER BY RAND(), as you know, because it will only generate one value. So use a key for a seed value.

SELECT RAND(object_id), object_id, name
  FROM sys.objects
 ORDER BY 1
harpo
  • 41,820
  • 13
  • 96
  • 131