Select Random Row from a table

Question

Post reply

Select Random Row from a table

FatL

SSC-Addicted

Points: 485
More actions
April 28, 2011 at 2:23 am

#99662

Hi there ,
Well i have a simple question . im working on a db where users send me SMS messages containing some context , after the day is over , i have to choose a winner based on some criteria of the sms body they have sent .
i was using SELECT TOP(1)* FROM TBL_WHATEVER ORDER BY NEWID() , but somehow i started feeling uncomfotable with this command because the Table gets really large and it takes time to select a random ,
Any suggestions ?
thanks in advance 😀

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Cadavre SSC-Forever Points: 41690 More actions · Answer 1

Something like this?

DECLARE @MaxValue INT, @MinValue INT

SET @MaxValue = 500

SET @MinValue = 1

SELECT * FROM (SELECT columns, ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber

FROM TBL_WHATEVER) t

WHERE t.rownumber = FLOOR(((@MaxValue + 1) - @MinValue) * RAND() + @MinValue)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

ChrisM@home SSC-Insane Points: 24260 More actions · Answer 2

If you have a sequential integer PK, then this is fast.

SELECT *

FROM TransactionDetail

WHERE TransactionDetailID = (SELECT ABS(CHECKSUM(NEWID()))%100000+1)

Replace 1000000 with the max ID in your table.

[font="Arial"]^{Low-hanging fruit picker and defender of the moggies}[/font]

For better assistance in answering your questions, please read this[/url].

Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/url]

Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]

FatL SSC-Addicted Points: 485 More actions · Answer 3

thanks chris ! its much faster now .. considering that this table im working on has 11000000 rows right now .. and grows more every minute 🙂

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 4

There is also the TABLESAMPLE clause:

http://msdn.microsoft.com/en-us/library/ms189108.aspx

From the article there is another way similar to ChrisM's method like this to get aprox 1% of rows:SELECT * FROM Sales.SalesOrderDetail

WHERE 0.01 >= CAST(CHECKSUM(NEWID(), SalesOrderID) & 0x7fffffff AS float)

/ CAST (0x7fffffff AS int)

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 5

I realize this is an old post but I have to ask... why would you feel that returning 110,000 (1% of the 11 million row table) would be a good thing instead of returning just the one row requested?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Lamprey13 SSCertifiable Points: 6210 More actions · Answer 6

It was just an example of other ways to get rangom rows.