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

The Argument Against Bit

I know a column of bit data type is very helpful as a flag or a code with only two valid values (three if you count NULL). However, I rarely use this data type to signify codes.

Say the column is used to identify an account status, which could be open or closed. More often than that, though, additional information is usually requested like when was it closed or when was it open. In this case, an AccountOpenDate and AccountCloseDate of date data type is more helpful.

I'm sure bit has its use, but sometimes I believe it's better to use a different data type that can hold other information than just three values.

As always, comments are welcome...


~~ CK

Product() Aggregate Funcion

According to BOL, aggregate functions perform a calculation on a set of values and return a single value. However, I can't find a PRODUCT() function. Looks like it's not one of the aggregate functions provided by T-SQL.

Consider this data set:

custid      Factor
----------- -----------
1           2
1           3
2           4
2           5
2           2
3           6
3           7
3           3
4           8
4           9

To produce this result set:

custid      Product
----------- ----------
1           6
2           40
3           126
4           72

Here's the code:

select custid, POWER(10.,SUM(LOG10(Factor)))
from SampleTable
group by custid

As usual, comments are welcome...


 ~~ CK