Click here to Skip to main content
11,478,066 members (72,478 online)
Click here to Skip to main content

How to make a random selection from an SQL table

, 2 Mar 2005 86.8K 41
Rate this:
Please Sign up or sign in to vote.
A simple technique for selecting random records from a 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.

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

Share

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 Pin
Omar Gamil18-Jun-11 5:06
memberOmar Gamil18-Jun-11 5:06 
Generaltrying to retrieve random questions from SQL Server Pin
Member 445285320-Feb-10 20:51
memberMember 445285320-Feb-10 20:51 
Generaltrying to retrieve random questions from SQL Server Pin
Member 445285320-Feb-10 20:43
memberMember 445285320-Feb-10 20:43 
GeneralCool tip Pin
aries78112-Aug-09 22:16
memberaries78112-Aug-09 22:16 
GeneralAmazing Pin
Member 33712744-Feb-09 17:53
memberMember 33712744-Feb-09 17:53 
GeneralGood idea but doesn't work on all servers Pin
MegosAR16-Oct-07 21:53
memberMegosAR16-Oct-07 21:53 
GeneralYou can do the same using Rand function right !! Pin
Himanshu Kumar Sinha12-Jun-06 0:07
memberHimanshu Kumar Sinha12-Jun-06 0:07 
GeneralFantastic But............ Pin
yasir102423-May-06 21:58
memberyasir102423-May-06 21:58 
GeneralGreat! Pin
takcrazy26-Apr-06 3:31
membertakcrazy26-Apr-06 3:31 
GeneralBrilliant Pin
furioso12-Mar-06 12:18
memberfurioso12-Mar-06 12:18 
QuestionFascinating -- where documented? Pin
davidw8801128-Mar-05 9:00
memberdavidw8801128-Mar-05 9:00 
AnswerRe: Fascinating -- where documented? Pin
davidw8801128-Mar-05 9:02
memberdavidw8801128-Mar-05 9:02 
Questionperformance hit? Pin
Ashley van Gerven4-Mar-05 13:55
memberAshley van Gerven4-Mar-05 13:55 
AnswerRe: performance hit? Pin
Stephen R5-Mar-05 6:04
sussStephen R5-Mar-05 6:04 
AnswerRe: performance hit? Pin
Rafael Araújo23-Mar-05 14:06
memberRafael Araújo23-Mar-05 14:06 
Generalgr8! Pin
Assaad Codoholic2-Mar-05 21:17
memberAssaad Codoholic2-Mar-05 21:17 
GeneralNice tip! - more info here... Pin
RayD2-Mar-05 6:59
memberRayD2-Mar-05 6: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 | Terms of Use | Mobile
Web03 | 2.8.150520.1 | Last Updated 2 Mar 2005
Article Copyright 2005 by Ach1lles
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid