SQL statement tip to randomize a Result Set (Order by NewId())

Contributed by aspfree
Rating:

/ 7
March 26, 2002

Rate this Article:
MEH MEH++

SEARCH ASP FREE

Randomize SQL Result set

I was recently looking for a method that would randomize a list of links displayed on ASPFree.com. Thanks to Scott Watermasysk (TripleASP.NET founder) passed along a tip that would randomize data coming from MS SQL Server. If the SQL statement that is SELECT * FROM TABLE ORDER BY NewId() , the returning results are scrambled. This was a nice way to re-arrange a result set. This is example can be used either in ASP.NET or classic ASP.

WebPage code

RandomSQL.aspx
<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.data" %>
<%@ Import Namespace="System.data.sqlclient" %>

<script language=vb runat=server>
Sub Page_Load(Sender as Object, E as EventArgs)
         Dim conn As SqlConnection = New SqlConnection("server=localhost;uid=sa;pwd=;database=databaseName")
         Dim cmd As SqlCommand = New SqlCommand("Select * From NewId Order by NewID()", conn)
         conn.Open()
         DataGrid1.DataSource = cmd.ExecuteReader
         DataGrid1.DataBind()
         conn.Close()
End Sub
</script>

<HTML>
         <HEAD><title>WebForm1</title></HEAD>
          <body>
              <form id="Form1" method="post" runat="server">
                   <asp:DataGrid id="DataGrid1" runat="server"></asp:DataGrid>
              </form>
          </body>
</HTML>

RandomSQL.asp

<%
          Dim conn, rs
          strconn = "Driver={SQL Server};Description=sqldemo;SERVER=127.0.0.1;UID=sa;PWD=;DATABASE=databaseName"

          set conn = server.createobject("adodb.connection")
          conn.open strconn
          set rs = conn.execute("Select * from NewId Order by NewId()")
%>
<html>
           <body>
                       <%
                            Do While Not rs.EOF
                                     response.write rs(0) & "<br>"
                                     rs.movenext
                            Loop

                            conn.close
                            set conn = nothing
                            set rs = nothing
                       %>
            </body>
<html>

Table DDL

CREATE TABLE [dbo].[NewId] (
[NewId] [int] NULL
) ON [PRIMARY]
GO

Sample Data used

1,2,3,4,5