Click here to Skip to main content
15,868,086 members
Articles / Web Development / ASP.NET
Article

How to make a random selection from an SQL table

Rate me:
Please Sign up or sign in to vote.
4.77/5 (27 votes)
2 Mar 2005 116.5K   40   17
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.

C#
//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


Written By
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 Gameel Salem18-Jun-11 4:06
professionalOmar Gameel Salem18-Jun-11 4:06 
Generaltrying to retrieve random questions from SQL Server Pin
Member 445285320-Feb-10 19:51
Member 445285320-Feb-10 19:51 
Generaltrying to retrieve random questions from SQL Server Pin
Member 445285320-Feb-10 19:43
Member 445285320-Feb-10 19:43 
GeneralCool tip Pin
aries78112-Aug-09 21:16
aries78112-Aug-09 21:16 
GeneralAmazing Pin
Member 33712744-Feb-09 16:53
Member 33712744-Feb-09 16:53 
GeneralGood idea but doesn't work on all servers Pin
AndresRohrDiartis16-Oct-07 20:53
AndresRohrDiartis16-Oct-07 20:53 
GeneralYou can do the same using Rand function right !! Pin
Himanshu Kumar Sinha11-Jun-06 23:07
Himanshu Kumar Sinha11-Jun-06 23:07 
GeneralFantastic But............ Pin
yasir102423-May-06 20:58
yasir102423-May-06 20:58 
GeneralGreat! Pin
takcrazy26-Apr-06 2:31
takcrazy26-Apr-06 2:31 
GeneralBrilliant Pin
furioso12-Mar-06 11:18
furioso12-Mar-06 11:18 
QuestionFascinating -- where documented? Pin
davidw8801128-Mar-05 8:00
davidw8801128-Mar-05 8:00 
AnswerRe: Fascinating -- where documented? Pin
davidw8801128-Mar-05 8:02
davidw8801128-Mar-05 8:02 
Questionperformance hit? Pin
Ashley van Gerven4-Mar-05 12:55
Ashley van Gerven4-Mar-05 12:55 
AnswerRe: performance hit? Pin
Stephen Robin5-Mar-05 5:04
Stephen Robin5-Mar-05 5:04 
AnswerRe: performance hit? Pin
Rafael Araújo23-Mar-05 13:06
Rafael Araújo23-Mar-05 13:06 
Generalgr8! Pin
Assaad Chalhoub2-Mar-05 20:17
Assaad Chalhoub2-Mar-05 20:17 
GeneralNice tip! - more info here... Pin
RayD2-Mar-05 5:59
RayD2-Mar-05 5:59 

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

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