how to choose a random record from a database

[Jimmy]

ill have a database with 1 table and 3 fields:

ID FIRSTNAME LASTNAME

(the ID field will be the auto incrementing index)

there might be 10 records in the DB, there might be 10,000.
i need to open the DB and randomly select a record (and then display the
name, which i dont have a problem with)
how can i randomly select a record? im guessing id have to open a recordset
and check the count to get the number of records, so lets say there were 100
records. i imagine i would have to generate a random number between 1 and
100....

anyone have a small example?

 

[Aaron Bertrand [SQL Server MVP]]

http://databases.aspfaq.com/database/how-do-i-retrieve-a-random-record.html

 

[Jimmy]

thanks... few questions though. here is the code:

<%
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
%>

what will randNum equal? a number between what and what?
in the SQL statement, what is "cols"? and "r"?

 

[Aaron Bertrand [SQL Server MVP]]

what will randNum equal? a number between what and what?

Hit refresh a few times:

<%
Randomize()
randNum = (CInt(1000 * Rnd) + 1) * -1
response.write (randNum)
%>

In the SQL statement, what is "cols"? and "r"?

cols is your column list.

r is just an alias for the random number, and it should be in the result set
because it use to seed the random number in the query and is used in the
order by. You could do it all in the order by but some queries will not
allow you to order by something that is not in the select list.

 

[Jimmy]

ok so

randNum = (CInt(9 * Rnd) + 1)

gives me 1-10, correct?
so to generate a random record from my database i would use:

randNum = (CInt((recordcount-1) * Rnd) + 1)


is that right?

 

[Aaron Bertrand [SQL Server MVP]]

Well, you have the same ability to try that code as I do. It really depends
on what you are then going to do with RandNum. What I was trying to
demonstrate is that you don't need to know the recordcount beforehand
(saving one query).

 

[Jimmy]

if i dont know the recordhand, how will i seed the random number generator?

obviously if i have 100 records i cant have a number generated thats over
100

 

[Aaron Bertrand [SQL Server MVP]]

Did you TRY the code sample that you had questions about? The random number
is not important, it is merely used to seed the random number in the query
that gets *1* row.

I suggest you try it out.

 

[Jimmy]

i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT random
record. do you see what im trying to do?

 

[Aaron Bertrand [SQL Server MVP]]

i dont have a database ready yet to TRY it. so instead im trying to

UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT
random record. do you see what im trying to do?

Sort of.

The problem is, if you have a number between 1 and 100, and you are trying
to get the row where [TableName]ID = that number, you're going to be
disappointed when you don't have a perfectly sequential set of [TableName]ID
values (even if the total number is exactly 100). Because of deletes,
rollbacks, and failures, you are likely going to have gaps in your
[TableName]ID. This is why the top solution is better than a solution that
relies on mapping recordcount to actual data.

A

 

[Bob Barrows [MVP]]

Does either the pubs or Northwind sample database come with SQLExpress?
If so, apply the example query to a table in one of those databases.

Let's look at the query:
sql = "SELECT TOP 1

TOP 1 tells it to return only the first record in the resultset

cols," & _

cols is meant to be a list of the columns you wish the query to return

"r = Rnd(" & randNum & ")" & _

This assigns a random number to each record in the resultset (prior to
TOP being applied)

"FROM TableName " & _
"ORDER BY r"

Without the "TOP 1", you would have a resultset containing the columns
specified by "cols" as well as a calculated column (called r) containing
a random number generated by the Rnd function, ordered by the random
number assigned to each record. The "TOP 1" returns the first one. So
you don't need to know how many records are in your table.

 

[Jimmy]

this is what i have working so far... tell me what you think:

<%
Dim oConn, oRS, randNum

Set oConn=Server.CreateObject("ADODB.Connection")
Set oRS=Server.CreateObject("ADODB.recordset")

oConn.Provider="Microsoft.Jet.OLEDB.4.0"
oConn.Open Server.MapPath("temp.mdb")

oRS.Open "SELECT EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic, adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("RecordCount: " & oRS.RecordCount & "<br><br>")

oRS.Move randNum
Response.Write oRS("EMAIL_ADDRESS")

oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
%>

this opens up the table, gets a count of num records, generates a random number between 0 and numrecords-1, then moves to that record, and displays a random email address.

seems like it works perfectly... do you see any issues?

i dont have a database ready yet to TRY it. so instead im trying to
UNDERSTAND it first.
my confusion is in the fact that im thinking if i have 100 records, i need
to generate a random number between 1 and 100 so that i can open THAT
random record. do you see what im trying to do?

Sort of.

The problem is, if you have a number between 1 and 100, and you are trying
to get the row where [TableName]ID = that number, you're going to be
disappointed when you don't have a perfectly sequential set of [TableName]ID
values (even if the total number is exactly 100). Because of deletes,
rollbacks, and failures, you are likely going to have gaps in your
[TableName]ID. This is why the top solution is better than a solution that
relies on mapping recordcount to actual data.

A

 

[Bob Barrows [MVP]]

this is what i have working so far... tell me what you think:
oRS.Open "SELECT EMAIL_ADDRESS FROM TABLE1", oConn, adOpenStatic,
adLockReadOnly
Randomize()
randNum = CInt((oRS.RecordCount - 1) * Rnd)

Response.Write("RecordCount: " & oRS.RecordCount & "<br><br>")

oRS.Move randNum

seems like it works perfectly... do you see any issues?

Yes. You're retrieving all the records from the database when you only
need one of them. Not a very efficient use of network or server
resources.

 

[Jimmy]

thank you.
im currently testing with an access db. do you see any issues with my
previous random record generating code?

 

[Jimmy]

ok i got ya...
so is there a way to "oRS.Move" to a particular record without having to do
it this way?
ie, can i open the DB to get a record count without SELECTing anything?

 

[Bob Barrows [MVP]]

Yes. Grossly inefficient. I would never do it that way. Let the database
do the job it can do so much more efficiently than any recordset/cursor
code you or I could write.

 

[Aaron Bertrand [SQL Server MVP]]

this opens up the table, gets a count of num records, generates a random
number between 0 and numrecords-1, then moves to that record, and displays a
random email address.

seems like it works perfectly... do you see any issues?
I suppose you haven't read any of my comments about why the suggested route
is better. For example, you don't need to pull the whole table to the
client in order to pick a random row.

If you are going to be using SQL Server Express, then you can say it as
simply as

SELECT TOP 1 Email_Address FROM Table1 ORDER BY NEWID();

(As the link suggested way back at the start of this thread, but I still
suppose you haven't read it (at least not in full).)

A

 

[Aaron Bertrand [SQL Server MVP]]

What, this?

Again, you need to show us what you are doing with randNum before we can
comment.

But yes, as we've already suggested, there are issues.

My guess is you are going to say

sql = "SELECT EmailAddress FROM Table1 WHERE PK = " & RandNum

And like I already commented, this won't work reliably. Never mind the
unnecessary roundtrip to count the number of rows in the table.

I think you need to stop theorizing code and deal with this when you can
actually test it against a real database and understand the differences and
what we are talking about. Until then it seems you are hellbent on just
doing it your way and ignoring our advice.

A

 

[Bob Barrows [MVP]]

You don't NEED oRs.Move. Using "TOP 1" you will only get a single record
back.

 

[Aaron Bertrand [SQL Server MVP]]

ie, can i open the DB to get a record count without SELECTing anything?

For the 15th time, YOU DON'T NEED A RECORD COUNT!