Retrieving Random Rows From a Database Table

Printer-friendly version

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.

Comments (0)

Related Blogs

TheReact Native Open Source roadmap was announced in Q4 2018 after they decided to invest more in the React Native open source community.

October is not just about pumpkins, fall foliage, and cooler temps anymore. October 2018 also means the exciting introduction of Microsoft Dynamics 365 for Customer Engagement.

Back in 2016, Microsoft introduced its intentions to refresh its CRM and ERP strategy with Dynamics 365. At the heart of its services was the Common Data Model (CDM).