Retrieving a Random Record from a Database

I was working on one of my new sites over the weekend and needed to retrieve a random record from one of the tables in my database. A few minutes of searching revealed several solutions.

Here's the SQL Server 2000 solution I ended up going with, although it might not work if you aren't using an integer as the record ID:

    SELECT TOP 1 *  
    FROM YourTable 
    ORDER BY NEWID() 

I found one to use if you're using IDENTITY as a unique idetifier for the record id, but I didn't test it since I'm using n auto-incrementing integer:

    SELECT TOP 1 *
    FROM YourTable 
    ORDER BY RAND((1000*IDColumn)*DATEPART(millisecond, GETDATE()))  

If you're using Access, here's one (although it's not nearly so elegant). This one requires some VBScript to generate the random number seed:

<% 
    Randomize() 
    randNum = (CInt(1000 * Rnd) + 1) * -1 
 
    set conn = CreateObject("ADODB.Connection") 
 
    sql = "SELECT TOP 1 cols," & _ 
        "r = Rnd(" & randNum & ")" & _ 
        "FROM TableName " & _ 
        "ORDER BY r" 
 
    set rs =     conn.execute(sql) 
 
    response.write rs(0) 
 
    ' ... 
    rs.close: set rs = nothing 
    conn.close: set conn = nothing 
%>