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