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.

ScreenShot1

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

ScreenShot2

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).

SELECT TOP <n>
FROM <table>
WHERE <criteria>
ORDER BY NEWID()

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

ScreenShot3

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.

Enjoy….

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

 
%d bloggers like this: