How to make a random selection from an SQL table

Introduction

This is one of the simplest solutions to a difficult problem I have ever come across. If like me, you have ever had the need to randomly select a number of database records from an SQL server (e.g. for banner ads or special offer selections) then this is an easy solution.

Solution

OK. So you have a table of records in an SQL database. Let's call the table "tbl_offers" for this example. In SQL you can assign a new ID value to any record which you select using the function "NewID()". This means if you order your records by this randomly generated ID field and select the top five or ten say, then every time you will get a different set of results.

Code

Please note I have written the following code only to demonstrate the use of the SQL and so have made it as simple as possible.

//This table can subsequently be used as a datasource
DataTable tbl_offers = new DataTable();

SqlConnection conn_offers = new SqlConnection(
    "Insert your DB Connection string here"
    );
SqlDataAdapter da_offers = new SqlDataAdapter(
     "SELECT Top 5 * FROM tbl_offers ORDER BY NewID()", 
      conn
    );
da_offers.Fill(tbl_offers);

And that's it. Very simple, very effective.

This article has no explicit license attached to it but may contain usage terms in the article text or the download files themselves. If in doubt please contact the author via the discussion board below.

A list of licenses authors might use can be found here