Click here to Skip to main content
Click here to Skip to main content

How to make a random selection from an SQL table

By , 2 Mar 2005
Rate this:
Please Sign up or sign in to vote.

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.

License

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

About the Author

Ach1lles
Founder Tech Dept
United Kingdom United Kingdom
My Evolution:
TRS-80 Basic, Clipper, C, Better Basic, FORTRAN, C++, Visual Basic, Delphi, C#

Comments and Discussions

 
GeneralLove it, Take 5 PinmemberOmar Gamil18-Jun-11 4:06 
Generaltrying to retrieve random questions from SQL Server PinmemberMember 445285320-Feb-10 19:51 
Generaltrying to retrieve random questions from SQL Server PinmemberMember 445285320-Feb-10 19:43 
GeneralCool tip Pinmemberaries78112-Aug-09 21:16 
GeneralAmazing PinmemberMember 33712744-Feb-09 16:53 
GeneralGood idea but doesn't work on all servers PinmemberMegosAR16-Oct-07 20:53 
GeneralYou can do the same using Rand function right !! PinmemberHimanshu Kumar Sinha11-Jun-06 23:07 
GeneralFantastic But............ Pinmemberyasir102423-May-06 20:58 
GeneralGreat! Pinmembertakcrazy26-Apr-06 2:31 
GeneralBrilliant Pinmemberfurioso12-Mar-06 11:18 
QuestionFascinating -- where documented? Pinmemberdavidw8801128-Mar-05 8:00 
AnswerRe: Fascinating -- where documented? Pinmemberdavidw8801128-Mar-05 8:02 
Questionperformance hit? PinmemberAshley van Gerven4-Mar-05 12:55 
AnswerRe: performance hit? PinsussStephen R5-Mar-05 5:04 
AnswerRe: performance hit? PinmemberRafael Araújo23-Mar-05 13:06 
Generalgr8! PinmemberAssaad Codoholic2-Mar-05 20:17 
GeneralNice tip! - more info here... PinmemberRayD2-Mar-05 5:59 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web01 | 2.8.140421.2 | Last Updated 2 Mar 2005
Article Copyright 2005 by Ach1lles
Everything else Copyright © CodeProject, 1999-2014
Terms of Use
Layout: fixed | fluid