Getting a Random Record Sample

Posted on 2011-10-17
Last Modified: 2012-05-12
I'm trying to produce a random sample of records from table PopulationBondsCash_Temp. This produces the total sample size for each user.  For example, if @Recordcount = 16, then this code produces 16 records for every UserID.  What I really need is that if the total sample size is 16 and then divide the total sample size evenly among each of the users so if there were 4 users there would we 4 records per user.  

DECLARE @Level Int;
SET @Level = 1;
DECLARE @RecordCount Int;
SET @RecordCount = NULL;

SELECT  @RecordCount = COUNT(*)
FROM    PopulationBondsCash_Temp

SELECT       ActivityDate,
      COUNT(UserID) OVER (Partition by UserID) AS UserCount,
      ROW_NUMBER() OVER (partition by UserID order by NEWID()) rn
FROM     PopulationBondsCash_Temp            
) a
where rn <= (CASE
    --Level 1
      WHEN @Level = 1 AND @RecordCount BETWEEN 2 AND 8 THEN 2
      WHEN @Level = 1 AND @RecordCount BETWEEN 9 AND 15 THEN 2
      WHEN @Level = 1 AND @RecordCount BETWEEN 16 AND 25 THEN 3
      WHEN @Level = 1 AND @RecordCount BETWEEN 26 AND 50 THEN 5      
      --Level 2
      WHEN @Level = 2 AND @RecordCount BETWEEN 2 AND 8 THEN 2
      WHEN @Level = 2 AND @RecordCount BETWEEN 9 AND 15 THEN 3
      WHEN @Level = 2 AND @RecordCount BETWEEN 16 AND 25 THEN 5
      WHEN @Level = 2 AND @RecordCount BETWEEN 26 AND 50 THEN 8      
      --Level 3
      WHEN @Level = 3 AND @RecordCount BETWEEN 2 AND 8 THEN 3
      WHEN @Level = 3 AND @RecordCount BETWEEN 9 AND 15 THEN 5
      WHEN @Level = 3 AND @RecordCount BETWEEN 16 AND 25 THEN 8
      WHEN @Level = 3 AND @RecordCount BETWEEN 26 AND 50 THEN 13      
Question by:evanburen
    LVL 142

    Accepted Solution

    what about using TABLESAMPLE ?

    anyhow, your requirement is not easy to be done in effient sql, at least not in 1 single pass, as you won't know the "total" until you actually run the sql.
    now, say there are N users and T records
    also, will there be T/N records for all N users?
    what if T is 21, and N = 4, where to "cut", and which "user" to cut?

    Author Comment

    Yea, it's pretty difficult.  I think I'll have to first count the total record count, then total for each UserID and then store those values in a table and then pull the sample based on those.


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    What Is Threat Intelligence?

    Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

    I am showing a way to read/import the excel data in table using SQL server 2005... Suppose there is an Excel file "Book1" at location "C:\temp" with column "First Name" and "Last Name". Now to import this Excel data into the table, we will use…
    Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
    This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor ( If you're looking for how to monitor bandwidth using netflow or packet s…
    Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now