How do I retrieve a random record? 5,884 requests - last updated Sunday, November 4, 2001   Assuming there is a unique identifier for each row, and that there is at least one record in the table, retrieving a random record can be quite easy. This method will work in SQL Server 7.0 and above (running on Windows 2000), but this could be a performance problem on larger tables / resultsets:    <%      SELECT TOP 1 someColumn      FROM someTable      ORDER BY NEWID()  %>   If you are not running on Windows 2000, then the following code will work if your table has a unique identifier (e.g. IDENTITY) column:    <%      SELECT TOP 1 someColumn      FROM someTable      ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))  %>   Note that both of these methods also allow you to select 10 or 50 or 5000 random records, simply by altering the TOP parameter.    SQL Server has a few other tricks up its sleeve as well...    Now, if you're stuck using Access or SQL Server 6.5, you may have to use some logic on the application end to deal with this. Here are the ordered steps your code must take: retrieve a recordcount, so you know how many records you need to "randomize" place the ID numbers in an array, one for each "hit" in your recordcount run the recordcount through a randomizer, and figure out which ID you're going to pick create a select statement matching the random number to its ID in the array Here is some code that will do this:    <%      set conn = Server.CreateObject("ADODB.Connection")      conn.open "<conn string>"        ' ***** (step 1) *****        set rs = conn.execute("SELECT COUNT(IDColumn) FROM someTable")      rCount = rs(0)        ' ***** (step 2) *****        set rs = conn.execute("SELECT IDColumn FROM someTable")      cnt = 1      dim RRs      redim RRs(rCount)      do while not rs.eof          RRs(cnt) = rs(0)          cnt = cnt + 1          rs.movenext      loop        ' ***** (step 3) *****        randomize      currentRR = cLng(rnd*rCount+0.5)      ID = RRs(currentRR)        ' ***** (step 4) *****        sql = "SELECT someColumn FROM someTable WHERE id=" & ID      set rs = conn.execute(sql)      response.write "ID # " & ID & " = " & rs(0)      ' ...      rs.close: set rs = nothing      conn.close: set conn = nothing  %>   With SQL Server, this would be much faster with a stored procedure... I just wanted to provide syntax here that demonstrates the concept, and will work on either Access or SQL Server.