Using NEWID to Randomly Sort Records

There's an easy way to Randomly Sort Records in SQL7 (on Windows 2000) and SQL2000. Using the NEWID() function will automatically generate a random UID. To use it to randomly sort records: SELECT * FROM mytable ORDER BY NEWID(); To randomly select a record from a table: SELECT TOP 1 * FROM mytable ORDER BY NEWID(); A very handy trick indeed.

Published February 16, 2004 · Updated September 17, 2005
Categorized as SQL
Short URL: https://snook.ca/s/146

Conversation

3 Comments · RSS feed
1
Guillermo said on August 10, 2005

Note that this doesn't always work! If you specify a WHERE clause with IN such as "where city in (7,10,345,1,500)" SQL will ALWAYS return records with CITY = 1 (the lowest value in the IN clause). This is not random at all.

2
Jonathan Snook said on August 10, 2005

Hmmm, was this on SQL7 or 2000? I just did a quick test in 2000 and still received random results using WHERE field IN (1, 2, ...)

3
Guillermo said on August 10, 2005

It's SQL2000 EE (Version 8.00.760 SP3). I tried "select * from mytable where xx in (300,200,400,1,345) order by newid()"
and I always get records with xx = 1, or whatever value I put in the IN clause which is lower than the rest. Note that there is an index (normal, not clustered) declared on XX.

Sorry, comments are closed for this post. If you have any further questions or comments, feel free to send them to me directly.