SQL Tips by Namwar Rizvi

May 5, 2007

Return a Random row from your query every time — Wonders of RowNum function

Filed under: Uncategorized — namwar @ 6:48 PM

Sometime we come across an odd requirement of returning a random row from the given TSQL Query. This is mostly required when you are designing a lottery system or random participant selection system in quiz show applications etc.
There are lots of different solutions to solve this problem but from TSQL point of view, I will recommend you to use following clean, understandable and maintainable query. It will always return a random row from the given resultset. It has been written in SQL Server 2005 TSQL format by using Row_Number() and Rand() function.

I have tested this query on a table of 100000 rows and it returns the random row instantaneously so you can use it safely on very large tables easily. The table I used in this example is tblItems which has two columns:

ItemId int and ItemName varchar(50)

—Total number of rows returned by the query
With tblTotalRows(totalRowCount)
as
(Select count(*) from tblItems),

–Query from which we need the random row
tblItemsWithRow(RowNum,ItemId,ItemName)
as
( Select Row_Number() Over(Order by ItemId) as RowNum, ItemID,ItemName
from tblItems

)

–Final Query to return the random row
Select RowNum,ItemId,ItemName
from tblItemsWithRow i inner join
(Select ceiling(totalRowCount * rand()) SelectRowNum from tblTotalRows) r On r.SelectRowNum=i.RowNum

Advertisements

2 Comments »

  1. What’s wrong with

    select top 1 <column list>
    from <table>
    order by newid()

    ?

    Does this example relate to a specific case that you had to solve?

    ML

    Comment by Matija Lah — May 9, 2007 @ 1:18 PM | Reply

  2. No, this query is not for specific case and you are right this is more quicker way to return a random row from the table.
    Thanks for your comments.
    Namwar Rizvi

    Comment by Namwar — May 9, 2007 @ 11:20 PM | Reply


RSS feed for comments on this post. TrackBack URI

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

Blog at WordPress.com.

%d bloggers like this: