Learn more about SQL Server tools

mssqltips logo
giveaway
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

SQL Server Random Sorted Result Set


By:   |   Read Comments (3)   |   Related Tips: More > T-SQL

Problem

How can I write a T-SQL query that will return rows sorted randomly?  Are there any SQL Server System Functions that are available or do I need to write custom code?  Check out this tip to learn more.

Solution

In this tip, we will show two ways to write a T-SQL SELECT query that returns rows that are sorted by a random number.

Let's begin by creating a table using the T-SQL CREATE TABLE statement below.

USE MSSQLTips
GO

create table TestData 
(
  pkID int identity(1,1) PRIMARY KEY,
  testNumber int not null,
  testCharacter varchar(1) not null
)

Next, we will populate our table with the T-SQL below.

insert into TestData values ( 100, 'a')
insert into TestData values ( 200, 'b')
insert into TestData values ( 300, 'c')
insert into TestData values ( 400, 'd')
insert into TestData values ( 500, 'e')
insert into TestData values ( 600, 'f')
insert into TestData values ( 700, 'g')
insert into TestData values ( 800, 'h')
insert into TestData values ( 900, 'i')
insert into TestData values (1000, 'j')

After inserting the records, let's review the data to make sure it is correct.

SELECT * FROM TestData

Review the data

Our first instinct might be to use the RAND() function without a seed value as a column or in the ORDER BY clause. As we see below, the unseeded RAND() function does not produce the desired results. The data returned by the query is still ordered by the primary key. Using the unseeded RAND() function as a column in the SELECT statement returns the same value on every row as shown in the SortOrder column.

SELECT RAND() as SortOrder, * 
FROM TestData 
ORDER BY SortOrder 

SELECT * 
FROM TestData 
ORDER BY RAND()

Undesired query results

To solve this problem, we need to pass a changing seed value to the RAND() function for every row to ensure that new random numbers are generated for each row. Furthermore, the seed value must be an integer expression. The NEWID() function will return a unique identifier for each row, but the data type returned is unique identifier which is not an integer expression. This will result in the error we see in the next image.

SELECT RAND(NEWID()) as SortOrder, * 
FROM TestData 
ORDER BY SortOrder

Incompatible data type error

To eliminate the data type incompatibility, we can pass the NEWID() value to the CHECKSUM() function. The CHECKSUM() function returns an integer data type, which can be passed to the RAND() function. We can nest these functions together to simplify our T-SQL code. In the next image, we can see the results when we use the seeded RAND() function as a column value and then ordering by this value.

SELECT RAND(CHECKSUM(NEWID())) as SortOrder, * 
FROM TestData 
ORDER BY SortOrder

Correct query results using RAND() as a column value

The example above works well if the random value column is required to be present in the rows returned by the query. There is a second approach we can take for returning rows that are sorted by a random value when we don't need the random value in our result set. This approach uses the NEWID() function alone in the ORDER BY clause as shown below.

SELECT * 
FROM TestData 
ORDER BY NEWID()

SELECT TOP 6 * 
FROM TestData 
ORDER BY NEWID()

Correct query results using NEWID() in the ORDER BY clause
Next Steps

Notice that each time the query is called, the order of the returned rows changes. We can also use this type of query to get a random sampling of our data by specifying the TOP number of rows.

Check out these other tips and tutorials on T-SQL and the RAND() function on MSSQLTips.com:



Last Update:






About the author
MSSQLTips author Dallas Snider Dr. Dallas Snider is an Assistant Professor in the Computer Science Department at the University of West Florida and has 18+ years of SQL experience.

View all my tips





More SQL Server Solutions











Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Notify for updates 


Get free SQL tips:

*Enter Code refresh code     



Tuesday, March 24, 2015 - 5:03:31 PM - George Back To Top

I recently came across an example using CAST(NEWID() AS VarBinary), instead of CHECKSUM(NEWID())

 

Which method do you think would typically be more efficient?

 

Excellent tip, thank you.


Tuesday, March 10, 2015 - 4:30:49 AM - Satnam Singh Back To Top

Great Article, very well appreciated.

 

 


Thursday, February 26, 2015 - 4:10:07 AM - Hardik Back To Top

Very help full article for all SQL Developer.

 

Thanks


Learn more about SQL Server tools