mssqlserver.com: FAQ: How can I select random rows from a SQL Server table?

How can I select random rows from a SQL Server table?

First you need to generate a random number - this assumes that you have some sort of numeric primary key on the table in the first place. Use something like :-

SELECT @NUM = RAND( (DATEPART(mm, GETDATE()) * 100000 )
+ (DATEPART(ss, GETDATE()) * 1000 )
+ DATEPART(ms, GETDATE()) )

(You could also use another variable like @@IDLE or @@CPU_BUSY to generate "random" numbers)

select @PK = min (col1) + (max (col1) - min (col1)) * @NUM

select * from <tbl> where col1 = @PK

Another way (courtesy of Joe Celko/Itzik Ben-Gan :-

SELECT S1.key_col
FROM SomeTable AS S1, SomeTable AS S2
WHERE S1.key_col <= S2.key_col
GROUP BY S1.key_col
HAVING COUNT(S2.key_col) =
(SELECT COUNT(*)
FROM SomeTable AS S3) * RAND() + 1;

Michael R. Hotek

All content on this site, except where noted, represents an original work of Michael R. Hotek and is protected by applicable copyright laws. The SQL Server FAQ is the sole work of Neil Pike. No page, portion of a page, or download may be used for commercial purposes in whole or in part without the express, written permission of the applicable author.