Fetching Random Rows In SQL Server

Recently I needed to fetch random rows from a SQL server table. If you have an integer column then using RAND() function goes well. However in my case there was no number column. In such cases you can use newid() function that is provide by SQL Server.

1). Create one Temporary table like Product.

   1: create table Product
   2: (
   3:     ProductId int,
   4:     ProductName varchar(100),
   5:     RetailPrice decimal(18,2),
   6:     ProductDesc varchar(1000)
   7: )

2). After Creating that Product Table Insert Few rows like 100 rows.

3). Now fire the query that mention belowed.

   1: select top 10 * from product 
   2: order by newid()

This query will return every time 10 random product.

This newid() function every time returns some random values. so when we order by from that function every time the product will different then the prev. 🙂

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