Source Code for http://www.brettb.com/SQL_Help_Randomly_Ordering.asp
<html>
<head>
<title>SQL Help: Generating Randomly Ordered SQL Server Result Sets</title>
<meta name="description"
content="This SQL Help article describes how to create randomly ordered SQL Server result sets by using the NewID() Transact SQL (TSQL) function">
<meta name="keywords"
content="tutorial, example, sql server, tsql, transact sql, sql, random, randomly, NewID">
<link REL="stylesheet" HREF="BrettbDotCom.css" TYPE="text/css">
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
</head>
<body>
<script language="JavaScript">
function SubmitHeaderSearchForm() {
document.FormSubmitHeaderSearch.submit();
}
</script>
<div align="center"><table border="0" cellpadding="0" cellspacing="0" width="800"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td width="167"><a href="Default.asp" title="Brettb.Com (Back to Home Page)"><img src="Images/BrettbDotCom_Logo.jpg" width="167" height="72"
alt="Brettb.Com" border="0"></a></td>
<td width="633"><img src="HeaderImages/PurpleFlame.jpg" width="633" height="72" alt=""></td>
</tr>
</table><table border="0" cellpadding="0" width="802" bgcolor="#DD3333"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0); border-top: 1px none rgb(0,0,0); border-bottom: 1px solid rgb(0,0,0)">
<tr>
<td class="TDHeader" valign="middle" align="left"> <a href="Default.asp"
title="HOME">HOME</a> | <a href="web.asp" title="ABOUT ME">ABOUT ME</a> | <a
href="Biotechnology.asp" title="BIOTECHNOLOGY">BIOTECHNOLOGY</a> | <a
href="technicalwriting.asp" title="ARTICLES">ARTICLES</a> | <a
href="DeveloperTools.asp" title="DEVELOPER TOOLS">TOOLS</a> | <a href="Gallery.asp"
title="GALLERY">GALLERY</a> | <a href="contact.asp" title="CONTACT">CONTACT</a></td>
<form method="POST" action="SearchResults.asp" name="FormSubmitHeaderSearch"><td align="right" class="TDHeader" valign="middle">Search: <input type="text" name="query"
size="20" maxlength="100"> <a href="javascript:SubmitHeaderSearchForm();">Go</a></td></form>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="8" cellspacing="0" width="802"
style="border-left: 1px solid rgb(0,0,0); border-right: 1px solid rgb(0,0,0)">
<tr>
<td bgcolor="#E0E0E0" width="151" valign="top" align="left" class="TDLeftPanel" nowrap><strong><a href="DeveloperTools.asp" title="DEVELOPER TOOLS">DEVELOPER TOOLS</a><br>
</strong> <a href="ASPDocumentationTool.asp" title="ASP Documentation Tool">ASP Doc
Tool</a><br>
<a href="ASPNetDocumentationTool.asp" title="ASP.NET Documentation Tool">ASP.NET Doc
Tool</a><br>
<a href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" title="SQL Documentation Tool" target="_blank">SQL Doc
Tool</a><br>
<a href="IndexServerCompanion.asp" title="Index Server Companion">Index Server
Companion</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><p><strong>TECHNICAL
ARTICLES <br>
</strong> <a href="ASPWatchArticles.asp" title="ASP Articles">ASP</a><br>
<a href="ASP.NETArticles.asp" title="ASP.NET Articles">ASP.NET</a><br>
<a href="JavaScriptArticles.asp" title="JavaScript Articles">JavaScript</a><br>
<a href="SQL_Help.asp" title="Transact SQL Articles">Transact SQL</a></p>
<p><strong>PHOTO GALLERIES</strong><br>
<a href="CanonEOS300D_Gallery1.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="Red_Arrows_2004.asp" title="Red Arrows 2004">Red Arrows 2004</a><br>
<a href="Living_Coasts_Photos.asp" title="Living Coasts">Living Coasts</a><br>
<a href="backgrounds.asp" title="Web Page Backgrounds">Web Page Backgrounds</a><br>
<a href="gallery.asp" title="More Galleries...">More Galleries...</a></p>
<p><strong>NEW STUFF</strong><br>
<a href="TransactSQLColorCoder.asp" title="Transact SQL Color Coder">SQL Color Coder</a><br>
<a href="CanonEOS300D_Gallery3.asp" title="Canon EOS 300D Samples">Canon EOS 300D
Samples</a><br>
<a href="TheWebsiteUtility.asp" title="The Website Utility">The Website Utility</a><br>
<a href="Website_Search_Engine_Optimisation.asp" title="Useful Search Engine Optimization Techniques">Search Engine Optimisation</a><br>
<a href="BuildingAnASPSearchEngine.asp" title="Creating an ASP Search Engine for your Website">Build an ASP Search Engine</a><br>
<a href="MyTropicalFishtank.asp" title="My Tropical Freshwater Fishtank">My Tropical Fishtank</a><br>
<a href="Investments_ISAs.asp" title="Savings & Investments">Savings & Investments</a><br>
<a href="what's_new.asp" title="What's New?">Other New Stuff...</a></p>
<p><strong>POPULAR STUFF</strong><br>
<a href="VBScriptRegularExpressions.asp" title="VBScript Regular Expressions">Regular
Expressions</a><br>
<a href="ASPDocumentationTool.asp">ASP Documentation Tool</a><br>
<a href="SearchingIndexServerWithASP.asp" title="Index Server & ASP">Index
Server & ASP</a><br>
<a href="js_banner_ad_rotator.asp" title="JavaScript Banner Ad Rotator">JavaScript Ad Rotator</a></p>
<p><strong>LINKS</strong><br>
<a href="http://www.winnershtriangle.com/w/Default.asp" title="Business Website"
target="_blank">Business Website</a><br>
<a href="http://authors.aspalliance.com/brettb/" title="ASPAlliance Articles"
target="_blank">ASPAlliance Articles</a><br>
</p>
<p><img alt="Microsoft Certified Professional"
src="images/MCP_c_smaller.gif" width="151" height="38"></p>
<p align="center">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_alternate_color = "FFFFFF";
google_ad_width = 120;
google_ad_height = 90;
google_color_border = "000000";
google_color_bg = "DDDDDD";
google_color_link = "FF0000";
google_color_url = "DD3333";
google_color_text = "CCCCCC";
google_ad_format = "120x90_0ads_al_s";
google_ad_channel ="6714185213";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
</p>
<p align="center">
<SCRIPT type='text/javascript' language='JavaScript' src='http://xslt.alexa.com/site_stats/js/s/a?amzn_id=brettbcom&url=www.brettb.com'></SCRIPT>
</p>
</td>
<td width="551" valign="top" align="left">
<p><a href="Default.asp" title="Home">Home</a> > <a href="technicalwriting.asp"
title="Articles">Articles</a> > <a href="SQL_Help.asp"
title="Transact SQL Programming Articles">Transact SQL Programming Articles</a></p>
<h1>Generating Randomly Ordered SQL Server Result Sets</h1>
<p>This article describes a straightforward method of generating randomly sorted result
sets in SQL Server. This procedure has a number of potential uses, such as displaying a
few randomly chosen news article headlines on a website, or it could be used to randomly
select a few advertisements while ensuring the same advert isn't always displayed at the
top of the advertising space.</p>
<p>Although it is possible to introduce randomness in SQL Server using time functions, in
practice this does not work (especially in stored procedures) because of the speed of
execution of the SQL statements [hence many or all of the rows could be returned in
exactly the same instant of time]. A far better alternative is, therefore, to use the <b>NewID</b>
function to create a unique identifier for each row returned. This returns GUID-like
identifiers such as <i>AF53DB47-766F-44B7-82EC-7459E353B3F3</i>.The results set can then
be ordered by this column.</p>
<p>The use of the <b>NewID</b> function is shown in this example stored procedure shown
below:</p>
<p><font color="#000080"><small>CREATE PROCEDURE sp_GetAdverts<br>
(<br>
@MaxNumberOfAdverts int<br>
)<br>
AS<br>
<br>
set rowcount @MaxNumberOfAdverts <br>
<br>
select top 100 t_Adverts.AdvertID,<br>
t_Adverts.TargetURL,<br>
t_Adverts.AltTag,<br>
<br>
newID() as 'RandomColumn'<br>
<br>
from t_Adverts<br>
where getdate() between t_Adverts.AdvertStartDate and t_Adverts.AdvertEndDate<br>
<br>
order by newID()<br>
GO</small></font></p>
<h2>Useful Links</h2>
<ul>
<li>The <strong><a
href="http://www.winnershtriangle.com/w/Products.SQLDocumentationTool.asp" target="_blank" title="SQL Documentation Tool">SQL
Documentation Tool</a></strong> builds technical documentation for Microsoft SQL Server
databases. A free trial version is available for download.</li>
</ul>
<ul>
<li>The <a href="http://www.winnershtriangle.com/w/Products.ASPDocumentationTool.asp"
title="ASP Documentation Tool">ASP Documentation Tool</a> can automatically document your
ASP (VBScript and JScript) websites and SQL Server/Microsoft Access databases. An <a
href="http://www.winnershtriangle.com/w/Products.ASPNETDocumentationTool.asp">ASP.NET
Documentation Tool</a> is also available!</li>
</ul>
<p>
</td>
<td width="100" valign="top" align="left">
<script type="text/javascript"><!--
google_ad_client = "pub-7044749527879330";
google_ad_width = 120;
google_ad_height = 600;
google_ad_format = "120x600_as";
google_ad_channel ="9212633149";
google_ad_type = "text_image";
google_color_border = "CCCCCC";
google_color_bg = "FFFFFF";
google_color_link = "0000FF";
google_color_url = "DD3333";
google_color_text = "000000";
google_alternate_ad_url = "http://www.brettb.com/NoGoogleAds.asp";
//--></script>
<script type="text/javascript"
src="http://pagead2.googlesyndication.com/pagead/show_ads.js">
</script>
<iframe marginwidth="0" marginheight="0" src="http://rcm.amazon.com/e/cm?t=brettbcom&o=1&p=10&l=st1&mode=books&search=sql server&=1&fc1=&lc1=<1=&bg1=&f=ifr" width="130" height="460" border="0" frameborder="0" style="border:none;" scrolling="no"></iframe>
</td>
</tr>
</table>
</center></div>
<div align="center"><center>
<table border="0" cellpadding="2" cellspacing="0" width="802"
style="border: 1px solid rgb(0,0,0)">
<tr>
<td class="TDFooter"> <a href="toc.asp" title="Site Map">Site Map</a></td>
<td class="TDFooter"><p align="right">All content is © 1995 - 2006 Brett Burridge</td>
</tr>
</table>
</center></div> </p>
</body>
</html>