SQL Tip #10 – Selecting Random Rows From a Table

Finding random records from a table might not be the important one. However, if you ever find yourself in a position to find random records from a table, then following could be a help:

Below sql queries will return random three(3) records from a table

Method 1: Using NEWID()
NEWID() will help us to generate random result set in a simple way. It creates a unique value of type uniqueidentifier. It will unique for each call. When it is a part of ORDER BY clause then it generates a unique value in memory for each row and then sorted in ascending order.

SELECT TOP 3 * FROM Customer ORDER BY NEWID()

 

Method 2: Using Tablesample()
TABLESAMPLE can quickly return a sample from a large table. TABLESAMPLE clause can be added to any table in the FROM clause. Percentage of rows or number of rows need to be specified as a part of this clause. However, this sampling method will return an approximate number of rows back. If you specify a small number, you might not be able to see any results. Rows returned by this clause are not in order and most likely to be random.

SELECT TOP 3 * FROM Customer Tablesample(1 percent)

If you want to get trully random records then user NEWID().

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s