Tuesday, April 23, 2013

Randomly selecting every Nth record

In my previous post, I showed how I randomly select X numbers of records. This time I was ask to randomly select every Nth record.

This query will select every 10th record in a random set:
;with RandomRecords
as
(
   select  
      rowid = row_number() over(order by newid()),
      <column list>
   from <tablename> t
   where <filtercondition>
)
select
   [Top X]
   *
from RandomRecords
where rowid % 10 = 0 and rowid <= (X * 10)
Now let's analyze the above code...

  • The order by newid() will result in randomly ordering the result set.
  • The TOP X is an optional clause if the requirement is to return only a subset of the entire data set, say the final set is 30,000 records, then X = 30,000.
  • The rowid % 10 = 0 ensures that the query will pick every 10th record.
  • The rowid <= (x * 10) further optimizes the query.


As always, comments are welcome...


~~ CK