public knowledge blog_load(sender Virendra)

                                                                                                                          Nothing shocks me, I am a Software Engineer.

Fetch random set of rows through SQL Query

Posted by Virendra Dugar on April 27, 2009

If you need to fetch random rows from a SQL server table then RAND() function will do the job for you.

For example:

select rand(), productid, productname from product

The problem with this method is that the Rand() function returns the same value for all rows within a given call, See image below.


You can also supply a seed value to the rand function to generate a unique value.

select top 5 rand(productid) as val ,productid, productname from product order by val


No matter, how many times you run this command, you will always get top 5 rows even though unique random is generated every time.

In such cases how to fetch random rows? SQL Server provides one more mechanism to access random rows. NewID(), which returns a GUID (Globally Unique Identifier).

FROM <table>
WHERE <criteria>

select top 5 newid() as val, productid, productname from Product order by val


The key is the use of NEWID() function that returns a GUID. Results will differ, since NewID() manufactures a GUID each time it is called. Therefore, if you run the above command two or three times, you will get different result sets.



Leave a Reply

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

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

Google+ photo

You are commenting using your Google+ 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 )


Connecting to %s

%d bloggers like this: