Returning a Random Set of Records

There are many business scenarios where an organization may find it extremely desirable to select a random set of records from a database. For example, an organization can use a random set of records to create a sample population for surveys, to reduce the size of a pool of prospects for a marketing campaign, or when analyzing data for statistical purposes.

The following example illustrates a common business use case that might require a random selection of data:

A marketing department decides that it wants to target 2,000 of its customers and prospects in a certain region of the world, perhaps North America.

A user creates a view in Aptify using demographic filters for North American countries. However, in this case, the view results contain over 7,000 records.

The marketing department wants to reduce the number of results to fit the sample size of 2,000 records. One way to accomplish this is to add additional filters to the view to target other customer attributes, such as business type.

However, in some cases, the marketing department may prefer to simply pick a sub-set of records from the base set. In this case, random selection is very useful.

Using Aptify, users can create views that return a defined number of records based on any combination of criteria, including the ability to return records randomly.

This section describes how to generate a random set of records based on a dynamically assigned Globally Unique Identifier (GUID).

The T-SQL NewID function generates a GUID value for each record using the MAC Address of the server's network card plus a unique number from the CPU clock. A GUID is a represented by 32 hexadecimal digits (0 - 9 and A - F) in the following format: xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx.

These values are random, so this function can serve as the basis for randomly selecting records from a view's resultset. Refer to Microsoft's SQL Server Books Online for more information on the NewID function.

Follow these steps to create a list view that returns records randomly based on a GUID assigned by the NewID function:

1. Create a new list view for a service.
   • For example, right-click the Persons service and select Create View to open the dialog for a new Persons view.
   • This approach can be used to create random selections for any top-level service in Aptify.
     
     
2. Enter a Name for the view.
3. Select List View as the View Type.
   
4. If you want to filter the records based on one or more filter criteria, click the Filters tab.
   • For example, you could add a country filter so the view only selects records randomly from those Persons in North American countries.
     
     
5. Click the Fields tab and specify the set of fields that should appear in the view. You can skip this step if you want to display the default set of fields.
6. Click the Paging tab and clear the Enable Paging option to disable paging.
7. Click the Advanced (SQL) tab.
8. Add the TOP keyword and the number of records to display after SELECT and before the list of fields, as shown below.
   
   • Example 1: To display 50 records, enter TOP 50.
   • Example 2: To display 10% of the total records, enter TOP 10 PERCENT.

   

9. At the end of the SQL statement, add an ORDER BY NewID() clause, as shown below.
   
10. Click OK to load the view.

• • The view displays a random set of records from the service. The number of records returned depends on the TOP keyword you specified.
    
    

.