Occasionally, I've needed to retrieve a random number of records from a database table for testing and found that the NEWID() function in SQL Server works well. By using the NEWID() function in the ORDER BY clause and setting a
TOP xx value, a random number of records can be returned from a table. There are similar approaches for each of the major databases (Oracle, PostgreSQL, MySQL, etc). The example below is using T-SQL and works for both SQL Server and Sybase. [code language="sql"] SELECT TOP 5 lastname, firstname, phone FROM dbo.customers ORDER BY NEWID() [/code] In MS Access you can achieve the same results using the RND() function in the ORDER BY clause with a SEED (an AutoNumber or DateTime column seems to work just fine). The RND() without a parameter returns a value between 0 and 1, but doesn’t regenerate for each row in the query and you end up with the same value for each row. The example below uses an AutoNumber column as the seed. [code language="sql"] SELECT TOP 5 lastname, firstname, phone FROM dbo.customers ORDER BY RND([key]); [/code]
About the Author:
TopLine Strategies delivers the complete integration and development of sales, marketing and customer service technologies that enable corporate clientele to improve revenue streams and strengthen customer interactions. Our project management and consulting is designed to achieve timely delivery, 100 percent user adoption of the technologies we implement and deliver measurable returns on investments for our clients.