random record with SELECT TOP does NOT work

[Evertjan.]

Re: random record with SELECT TOP does NOT work

Dave Anderson wrote on 13 sep 2006 in
microsoft.publi c.inetserver.as p.general:

http://en.wikipedia.org/wiki/KISS_Principle

As Edsger Wybe Dijkstra (1930-2002),
the famours opponent of the "goto" statement,
said:

"Simplicity is a prerequisite voor reliability"

<http://en.wikipedia.or g/wiki/Edsger_Dijkstra >

--
Evertjan.
The Netherlands.
(Please change the x'es to dots in my emailaddress)

[Jimmy]

Re: random record with SELECT TOP does NOT work


"Mike Brind" <paxtonend@hotm ail.comwrote in message
news:1158132231 .703401.227700@ e63g2000cwd.goo glegroups.com.. .

>
Jimmy wrote:

>"Larry Bud" <larrybud2002@y ahoo.comwrote in message
>news:115808598 3.171080.45690@ p79g2000cwp.goo glegroups.com.. .

>
Jimmy wrote:
>"Larry Bud" <larrybud2002@y ahoo.comwrote in message
>news:115800045 2.100017.42550@ i3g2000cwc.goog legroups.com...
>
Jimmy wrote:
>thanks to everyone that helped, unfortunately the code samples
>people
>gave me don't work. here is what i have so far:
>>
><%
>Dim oConn, oRS, randNum
>Randomize()
>randNum = (CInt(1000 * Rnd) + 1) * -1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>Set oRS=Server.Crea teObject("ADODB .recordset")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>>
>oRS.Open "SELECT TOP 1 EMAIL_ADDRESS, r = Rnd(" & randNum & ") FROM
>TABLE1 ORDER BY r", oConn, adOpenStatic, adLockReadOnly
>>
>Response.Wri te oRS("EMAIL_ADDR ESS")
>>
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>%>
>
That SQL definitely does NOT work in Access 2003. It ends up giving
the column R the same value for each row.
>
Here's the real answer
>
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2;
>
where mytable is your table that contains email_address and ID. ID
is
important, as it's a unique integer for each row. It doesn't matter
how the rows are numbered, but as long as each one is unique, you
get a
different value for the 2nd column on each row. ORDER BY 2 orders
by
the 2nd column listed.
>
NOW, PLEASE DO NOT TOP POST. Top posting is posting your answer to
a
message BEFORE or ABOVE or ON TOP of the previous messages. The
problem is that if someone needs to read the whole posted thread,
they
have to start at the bottom and work their way to the top. It'd be
like reading a newspaper column a paragraph at a time, starting with
the last paragraph.
>
Hope this helps.
>
>>
>ok, your code always displays the 4th record. here is the entire code
>from
>my test page (thank you for your time, by the way)
>>
>Dim oConn, oRS, randNum
>Randomize()
>randNum = (CInt(1000 * Rnd) + 1) * -1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>>
>Set oRS=oConn.Execu te("SELECT TOP 1 EMAIL_ADDRESS, Rnd(ID) FROM TABLE1
>ORDER
>BY 2")
>Response.Wri te oRS("EMAIL_ADDR ESS")
>>
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>
Did you the instructions and add an "ID" column, and populate it with
unique records?
>
Works on my end in Access 2003.
>

>>
>>
>>
>well thats weird. yes, i have a column named "ID" that is the primary key
>and autoincrements. there are only 4 records in the database, the ID
>field
>is unique in each record. here is the code in my ASP page:
>>
><%
>Dim oConn, oRS, randNum
>Randomize()
>randNum = (CInt(1000 * Rnd) + 1) * -1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>Set oRS=oConn.Execu te("SELECT TOP 1 EMAIL_ADDRESS, Rnd(ID) FROM TABLE1
>ORDER
>BY 2")
>Response.Wri te oRS("EMAIL_ADDR ESS")
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>%>
>>
>this displays the second record ALWAYS. im thinking something to do with
>the
>Rnd(ID) function... either way, i can probably delete the "randNum" line,
>correct? but this still doesnt work...

>
Try this approach. It's from the same guy who apparently supplied
aspfaq.com (Ken Schaefer) with the erroneous code, but this one works:
>

http://www.adopenstatic.com/faq/RandomRecord.asp

>
Larry's code also works for me.
>
--
Mike Brind
>

this is killing me....
here is the code from that link:

' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1

' Return 3 random records
strSQL = _
"SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
"*TableID)" & _
"FROM Table1 " & _
"ORDER BY 3"

Set objRS = objConn.Execute (strSQL)

im trying to make it work with a table that has an ID column, and an
EMAIL_ADDRESS column, and i need it to only return 1 row.
here is my code:

Dim oConn, oRS, intRandomNumber
Randomize()
intRandomNumber = Int(1000*Rnd)+1
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
(intRandomNumbe r) & "*ID) FROM TABLE1")
Response.Write oRS("EMAIL_ADDR ESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing

and guess what? ALWAYS returns the same email address. what am i doing wrong
here?

[Aaron Bertrand [SQL Server MVP]]

Re: random record with SELECT TOP does NOT work

this is killing me....

here is the code from that link:
>
' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1
>
' Return 3 random records
strSQL = _
"SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
"*TableID)" & _
"FROM Table1 " & _
"ORDER BY 3"
>
Set objRS = objConn.Execute (strSQL)
>
im trying to make it work with a table that has an ID column, and an
EMAIL_ADDRESS column, and i need it to only return 1 row.
here is my code:
>
Dim oConn, oRS, intRandomNumber
Randomize()
intRandomNumber = Int(1000*Rnd)+1
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
(intRandomNumbe r) & "*ID) FROM TABLE1")
Response.Write oRS("EMAIL_ADDR ESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
>
and guess what? ALWAYS returns the same email address. what am i doing
wrong here?
>
>

Are we still on this? Is this the thread that will never end?

Your code is not the same as the article's. Where's your ORDER BY? Without
an ORDER BY clause, Access is going to return you the rows in the order it
deems appropriate. Unless there is heavy modification to the table between
runs, this is not going to change.

What version of Access are you using? Maybe most of us are trying the code
on a newer version which handles Rnd() better/correctly.

Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

[Jimmy]

Re: random record with SELECT TOP does NOT work


"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:OkJrqdz1GH A.2196@TK2MSFTN GP06.phx.gbl...

>this is killing me....
>here is the code from that link:
>>
>' Initialize ASP RND() function
>Randomize()
>intRandomNumbe r = Int (1000*Rnd)+1
>>
>' Return 3 random records
>strSQL = _
> "SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
>"*TableID)" & _
> "FROM Table1 " & _
> "ORDER BY 3"
>>
>Set objRS = objConn.Execute (strSQL)
>>
>im trying to make it work with a table that has an ID column, and an
>EMAIL_ADDRES S column, and i need it to only return 1 row.
>here is my code:
>>
>Dim oConn, oRS, intRandomNumber
>Randomize()
>intRandomNumbe r = Int(1000*Rnd)+1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
>(intRandomNumb er) & "*ID) FROM TABLE1")
>Response.Wri te oRS("EMAIL_ADDR ESS")
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>>
>and guess what? ALWAYS returns the same email address. what am i doing
>wrong here?
>>
>>

>
Are we still on this? Is this the thread that will never end?
>
Your code is not the same as the article's. Where's your ORDER BY?
Without an ORDER BY clause, Access is going to return you the rows in the
order it deems appropriate. Unless there is heavy modification to the
table between runs, this is not going to change.
>
What version of Access are you using? Maybe most of us are trying the
code on a newer version which handles Rnd() better/correctly.
>
Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

>

;o)

access 2003. i removed the order by clause because im now only returning 1
row... my thought was that theres nothing to order by(?)
what should i be ordering by? ill try it right away

[Aaron Bertrand [SQL Server MVP]]

Re: random record with SELECT TOP does NOT work

access 2003. i removed the order by clause because im now only returning 1

row... my thought was that theres nothing to order by(?)

Have you not been paying attention at all?

The whole purpose of the random number is to apply a random number to each
row. Then, to pick a random row, you select TOP 1 and order by the random
number. See? Every time it runs, rows will get a different distribution of
random numbers.

what should i be ordering by? ill try it right away

UH, JUST LIKE THE CODE SAMPLE. You are ordering by the random number, which
is the 3rd column, so copy and paste:

ORDER BY 3

[Mike Brind]

Re: random record with SELECT TOP does NOT work


Jimmy wrote:

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in message
news:OkJrqdz1GH A.2196@TK2MSFTN GP06.phx.gbl...

this is killing me....
here is the code from that link:
>
' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1
>
' Return 3 random records
strSQL = _
"SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
"*TableID)" & _
"FROM Table1 " & _
"ORDER BY 3"
>
Set objRS = objConn.Execute (strSQL)
>
im trying to make it work with a table that has an ID column, and an
EMAIL_ADDRESS column, and i need it to only return 1 row.
here is my code:
>
Dim oConn, oRS, intRandomNumber
Randomize()
intRandomNumber = Int(1000*Rnd)+1
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
(intRandomNumbe r) & "*ID) FROM TABLE1")
Response.Write oRS("EMAIL_ADDR ESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
>
and guess what? ALWAYS returns the same email address. what am i doing
wrong here?
>
>

Are we still on this? Is this the thread that will never end?

Your code is not the same as the article's. Where's your ORDER BY?
Without an ORDER BY clause, Access is going to return you the rows in the
order it deems appropriate. Unless there is heavy modification to the
table between runs, this is not going to change.

What version of Access are you using? Maybe most of us are trying the
code on a newer version which handles Rnd() better/correctly.

Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

>
;o)
>
access 2003. i removed the order by clause because im now only returning 1
row... my thought was that theres nothing to order by(?)
what should i be ordering by? ill try it right away

As Aaron effectively pointed out, how can you possibly expect code
samples to work correctly if you modify them based on what you *think*
looks right? You may well say that you still don't fully understand
the theory behind how it all works (although enough explanations have
been given here), and if that is the case, that's even less reason to
tinker with the sample.

In any database-related code sample, the only things you should be
looking to change are the connection string, db name, table, and column
names to suit your environment. Changing the fundamental SQL statement
that the code sample offers is totally pointless.

Understanding the theory is, of course important. But I would suggest
that since you are obviously not going through any formal learning
here, you should focus on getting it to work. The theory side of it
will all fall into place as you go along.

--
Mike Brind

[Jimmy]

Re: random record with SELECT TOP does NOT work


"Mike Brind" <paxtonend@hotm ail.comwrote in message
news:1158158865 .613183.275500@ i42g2000cwa.goo glegroups.com.. .

>
Jimmy wrote:

>"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
>message
>news:OkJrqdz1G HA.2196@TK2MSFT NGP06.phx.gbl.. .

>this is killing me....
>here is the code from that link:
>>
>' Initialize ASP RND() function
>Randomize()
>intRandomNumbe r = Int (1000*Rnd)+1
>>
>' Return 3 random records
>strSQL = _
> "SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
>"*TableID)" & _
> "FROM Table1 " & _
> "ORDER BY 3"
>>
>Set objRS = objConn.Execute (strSQL)
>>
>im trying to make it work with a table that has an ID column, and an
>EMAIL_ADDRES S column, and i need it to only return 1 row.
>here is my code:
>>
>Dim oConn, oRS, intRandomNumber
>Randomize()
>intRandomNumbe r = Int(1000*Rnd)+1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
>(intRandomNumb er) & "*ID) FROM TABLE1")
>Response.Wri te oRS("EMAIL_ADDR ESS")
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>>
>and guess what? ALWAYS returns the same email address. what am i doing
>wrong here?
>>
>>
>
Are we still on this? Is this the thread that will never end?
>
Your code is not the same as the article's. Where's your ORDER BY?
Without an ORDER BY clause, Access is going to return you the rows in
the
order it deems appropriate. Unless there is heavy modification to the
table between runs, this is not going to change.
>
What version of Access are you using? Maybe most of us are trying the
code on a newer version which handles Rnd() better/correctly.
>
Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

>

>>
>;o)
>>
>access 2003. i removed the order by clause because im now only returning
>1
>row... my thought was that theres nothing to order by(?)
>what should i be ordering by? ill try it right away

>
As Aaron effectively pointed out, how can you possibly expect code
samples to work correctly if you modify them based on what you *think*
looks right? You may well say that you still don't fully understand
the theory behind how it all works (although enough explanations have
been given here), and if that is the case, that's even less reason to
tinker with the sample.
>
In any database-related code sample, the only things you should be
looking to change are the connection string, db name, table, and column
names to suit your environment. Changing the fundamental SQL statement
that the code sample offers is totally pointless.
>
Understanding the theory is, of course important. But I would suggest
that since you are obviously not going through any formal learning
here, you should focus on getting it to work. The theory side of it
will all fall into place as you go along.
>
--
Mike Brind
>

thank you. this sql string appears to work:

SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 * (intRandomNumbe r) & "*ID) FROM
TBL_SECRETS ORDER BY 3

however i have seen so many different examples and variations on this string
(most of which were wrong and did not work) that i obviously started playing
myself. my confusion here was that i did not know that ORDER BY 3 ordered by
the 3rd column, i thought it was ordering by the number 3, which i did not
understand.

if you go back and look at some of the other examples given, there are a few
where the ID column was not selected, and was not part of the multiplication
in the Rnd function. so my question is, do i NEED to select this column?
(many people here obviously thought i didnt) so i just want to make this
string as simple as possible.

thank you

[Mike Brind]

Re: random record with SELECT TOP does NOT work


Jimmy wrote:

"Mike Brind" <paxtonend@hotm ail.comwrote in message
news:1158158865 .613183.275500@ i42g2000cwa.goo glegroups.com.. .

Jimmy wrote:

"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
message
news:OkJrqdz1GH A.2196@TK2MSFTN GP06.phx.gbl...
this is killing me....
here is the code from that link:
>
' Initialize ASP RND() function
Randomize()
intRandomNumber = Int (1000*Rnd)+1
>
' Return 3 random records
strSQL = _
"SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
"*TableID)" & _
"FROM Table1 " & _
"ORDER BY 3"
>
Set objRS = objConn.Execute (strSQL)
>
im trying to make it work with a table that has an ID column, and an
EMAIL_ADDRESS column, and i need it to only return 1 row.
here is my code:
>
Dim oConn, oRS, intRandomNumber
Randomize()
intRandomNumber = Int(1000*Rnd)+1
Set oConn=Server.Cr eateObject("ADO DB.Connection")
oConn.Provider= "Microsoft.Jet. OLEDB.4.0"
oConn.Open Server.MapPath( "temp.mdb")
Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
(intRandomNumbe r) & "*ID) FROM TABLE1")
Response.Write oRS("EMAIL_ADDR ESS")
oRS.close
oConn.close
Set oConn = nothing
Set oRS = nothing
>
and guess what? ALWAYS returns the same email address. what am i doing
wrong here?
>
>

Are we still on this? Is this the thread that will never end?

Your code is not the same as the article's. Where's your ORDER BY?
Without an ORDER BY clause, Access is going to return you the rows in
the
order it deems appropriate. Unless there is heavy modification to the
table between runs, this is not going to change.

What version of Access are you using? Maybe most of us are trying the
code on a newer version which handles Rnd() better/correctly.

Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

>
;o)
>
access 2003. i removed the order by clause because im now only returning
1
row... my thought was that theres nothing to order by(?)
what should i be ordering by? ill try it right away

As Aaron effectively pointed out, how can you possibly expect code
samples to work correctly if you modify them based on what you *think*
looks right? You may well say that you still don't fully understand
the theory behind how it all works (although enough explanations have
been given here), and if that is the case, that's even less reason to
tinker with the sample.

In any database-related code sample, the only things you should be
looking to change are the connection string, db name, table, and column
names to suit your environment. Changing the fundamental SQL statement
that the code sample offers is totally pointless.

Understanding the theory is, of course important. But I would suggest
that since you are obviously not going through any formal learning
here, you should focus on getting it to work. The theory side of it
will all fall into place as you go along.

--
Mike Brind

>
>
thank you. this sql string appears to work:
>
SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 * (intRandomNumbe r) & "*ID) FROM
TBL_SECRETS ORDER BY 3
>
however i have seen so many different examples and variations on this string
(most of which were wrong and did not work)

The only one I couldn't get to work was the one on aspfaq.com, (which
is unusual). It may be that it works in previous versions of Access.
I don't know.

that i obviously started playing
myself. my confusion here was that i did not know that ORDER BY 3 ordered by
the 3rd column, i thought it was ordering by the number 3, which i did not
understand.

A full and complete explanation of this was provided by Bob Barrows
very early on in this thread, where he discusses the fact that you
can't order by an alias in Jet SQL, so you need to order by the ordinal
position of the calculated column. Aaron has clarified why you need to
order by the calculated column a number of times in all the threads you
have started on this topic.

>
if you go back and look at some of the other examples given, there are a few
where the ID column was not selected, and was not part of the multiplication
in the Rnd function. so my question is, do i NEED to select this column?
(many people here obviously thought i didnt) so i just want to make this
string as simple as possible.

No, you only need to select the column(s) that you want to display AND
the calculated column. Hopefully, you will by now understand that if
you omit the ID column from your SELECT clause, you will only be
selecting 2 columns, and therefore the calculated column will be the
2nd of these. Consequently you will have to amend your ORDER BY to 2.
Equally, if you ADD extra columns to your SELECT clause, put them
before Rnd(" & -1 * (intRandomNumbe r) & "*ID), and amend the position
of the column to ORDER BY accordingly.

I didn't know any of this before your questions started, because I have
never needed to. Everything I have picked up has been from the
relevant threads. Bob, Aaron and others have explained all this
clearly enough, and often enough that, as Aaron said, this thread
should have been over long ago. It probably would have been if you'd
managed to keep Bob on your side. But you messed that up too.

--
Mike Brind

[Jimmy]

Re: random record with SELECT TOP does NOT work


"Mike Brind" <paxtonend@hotm ail.comwrote in message
news:1158162428 .848120.135760@ e63g2000cwd.goo glegroups.com.. .

>
Jimmy wrote:

>"Mike Brind" <paxtonend@hotm ail.comwrote in message
>news:115815886 5.613183.275500 @i42g2000cwa.go oglegroups.com. ..

>
Jimmy wrote:
>"Aaron Bertrand [SQL Server MVP]" <ten.xoc@dnartr eb.noraawrote in
>message
>news:OkJrqdz1G HA.2196@TK2MSFT NGP06.phx.gbl.. .
>this is killing me....
>here is the code from that link:
>>
>' Initialize ASP RND() function
>Randomize()
>intRandomNumbe r = Int (1000*Rnd)+1
>>
>' Return 3 random records
>strSQL = _
> "SELECT TOP 3 TableID, Field1, Rnd(" & -1 * (intRandomNumbe r) &
>"*TableID)" & _
> "FROM Table1 " & _
> "ORDER BY 3"
>>
>Set objRS = objConn.Execute (strSQL)
>>
>im trying to make it work with a table that has an ID column, and
>an
>EMAIL_ADDRES S column, and i need it to only return 1 row.
>here is my code:
>>
>Dim oConn, oRS, intRandomNumber
>Randomize()
>intRandomNumbe r = Int(1000*Rnd)+1
>Set oConn=Server.Cr eateObject("ADO DB.Connection")
>oConn.Provider ="Microsoft.Jet .OLEDB.4.0"
>oConn.Open Server.MapPath( "temp.mdb")
>Set oRS=oConn.Execu te("SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 *
>(intRandomNumb er) & "*ID) FROM TABLE1")
>Response.Wri te oRS("EMAIL_ADDR ESS")
>oRS.close
>oConn.close
>Set oConn = nothing
>Set oRS = nothing
>>
>and guess what? ALWAYS returns the same email address. what am i
>doing
>wrong here?
>>
>>
>
Are we still on this? Is this the thread that will never end?
>
Your code is not the same as the article's. Where's your ORDER BY?
Without an ORDER BY clause, Access is going to return you the rows
in
the
order it deems appropriate. Unless there is heavy modification to
the
table between runs, this is not going to change.
>
What version of Access are you using? Maybe most of us are trying
the
code on a newer version which handles Rnd() better/correctly.
>
Is it possible the page is caching in your browser?

http://classicasp.aspfaq.com/general/how-do-i-prevent-my-asp-pages-from-caching.html

>
>>
>;o)
>>
>access 2003. i removed the order by clause because im now only
>returning
>1
>row... my thought was that theres nothing to order by(?)
>what should i be ordering by? ill try it right away
>
As Aaron effectively pointed out, how can you possibly expect code
samples to work correctly if you modify them based on what you *think*
looks right? You may well say that you still don't fully understand
the theory behind how it all works (although enough explanations have
been given here), and if that is the case, that's even less reason to
tinker with the sample.
>
In any database-related code sample, the only things you should be
looking to change are the connection string, db name, table, and column
names to suit your environment. Changing the fundamental SQL statement
that the code sample offers is totally pointless.
>
Understanding the theory is, of course important. But I would suggest
that since you are obviously not going through any formal learning
here, you should focus on getting it to work. The theory side of it
will all fall into place as you go along.
>
--
Mike Brind
>

>>
>>
>thank you. this sql string appears to work:
>>
>SELECT TOP 1 ID, EMAIL_ADDRESS, Rnd(" & -1 * (intRandomNumbe r) & "*ID)
>FROM
>TBL_SECRETS ORDER BY 3
>>
>however i have seen so many different examples and variations on this
>string
>(most of which were wrong and did not work)

>
The only one I couldn't get to work was the one on aspfaq.com, (which
is unusual). It may be that it works in previous versions of Access.
I don't know.
>

>that i obviously started playing
>myself. my confusion here was that i did not know that ORDER BY 3 ordered
>by
>the 3rd column, i thought it was ordering by the number 3, which i did
>not
>understand.

>
A full and complete explanation of this was provided by Bob Barrows
very early on in this thread, where he discusses the fact that you
can't order by an alias in Jet SQL, so you need to order by the ordinal
position of the calculated column. Aaron has clarified why you need to
order by the calculated column a number of times in all the threads you
have started on this topic.
>

>>
>if you go back and look at some of the other examples given, there are a
>few
>where the ID column was not selected, and was not part of the
>multiplicati on
>in the Rnd function. so my question is, do i NEED to select this column?
>(many people here obviously thought i didnt) so i just want to make this
>string as simple as possible.

>
No, you only need to select the column(s) that you want to display AND
the calculated column. Hopefully, you will by now understand that if
you omit the ID column from your SELECT clause, you will only be
selecting 2 columns, and therefore the calculated column will be the
2nd of these. Consequently you will have to amend your ORDER BY to 2.
Equally, if you ADD extra columns to your SELECT clause, put them
before Rnd(" & -1 * (intRandomNumbe r) & "*ID), and amend the position
of the column to ORDER BY accordingly.
>
I didn't know any of this before your questions started, because I have
never needed to. Everything I have picked up has been from the
relevant threads. Bob, Aaron and others have explained all this
clearly enough, and often enough that, as Aaron said, this thread
should have been over long ago. It probably would have been if you'd
managed to keep Bob on your side. But you messed that up too.
>
--
Mike Brind
>

last post on this topic....

just tell me if this looks ok.
i have a stored query named "sp_random" that looks like this:

SELECT TOP 1 EMAIL_ADDRESS, Rnd(@intRandomN umber * ID)
FROM TABLE1
ORDER BY 2;

then this ASP code:

Randomize()
intRandomNumber = (CInt(1000 * Rnd) + 1) * -1
Set oRS=oConn.Execu te("EXEC sp_random " & intRandomNumber )

this appears to work, but just wanted to make sure you didnt see problems
with it

thank you

[Dave Anderson]

Re: random record with SELECT TOP does NOT work

Mike Brind wrote:

A full and complete explanation of this was provided by Bob
Barrows very early on in this thread, where he discusses the
fact that you can't order by an alias in Jet SQL, so you need
to order by the ordinal position of the calculated column.

I generally try to stay out of Access threads, but I have been following
this one, and I have to ask why anyone even bothers to create a column for
the random value. It is not required.

Working from the tried & true SQL Server favorite...

SELECT ... ORDER BY NEWID()

....I wondered if the same would work in Access. So I opened a connection to
the Northwind database and tried:

RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(EmployeeID* " + -Math.random() + ")"
)

This is obviously in JScript, but the principle is the same -- There is no
need to squabble over the column number, since we do not return the random
number in a column.

This particular example is a poor random generator -- I ran this 10,000
times and got a distribution like this:

Record 1 : 100
Record 2 : 122
Record 3 : 1526
Record 4 : 1584
Record 5 : 1302
Record 6 : 1412
Record 7 : 1232
Record 8 : 1487
Record 9 : 1235

When normalized so the highest value is 100, this has a standard deviation
of 36.64. Not good. But when I added another Rnd to the mix, the
distribution got flatter:

RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(Rnd(Employe eID*" + -Math.random() + "))"
)

Record 1 : 1462
Record 2 : 1389
Record 3 : 1115
Record 4 : 937
Record 5 : 982
Record 6 : 1027
Record 7 : 1094
Record 8 : 996
Record 9 : 998

Normalized standard deviation: 12.81. I ran this test a couple of times, and
noticed a slight bias toward record 1, so I jacked it up to 50,000 (this
required me to pump up Server.ScriptTi meout), and got this:

7504
7282
5350
4723
4818
5329
5158
4825
5011

(Std dev: 14.21). There definitely seems to be a bias toward the lowest
values. I made one last adjustment (sign change on inner Rnd)...

RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(-Rnd(EmployeeID* " + -Math.random() + "))"
)

....and ran it 180,000 times. This yielded the following distribution:

20529
22977
20013
17616
19926
21250
20085
17665
19939

This is much better, with normalized standard deviation of 7.19. Probably
random enough for most needs. My conclusion? Jimmy could get by with this:

Randomize()
Set oRS=oConn.Execu te("SELECT TOP 1 EMAIL_ADDRESS FROM TABLE1 ORDER BY
Rnd(-Rnd(ID*" & -Rnd & "))")



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Jimmy]

Re: random record with SELECT TOP does NOT work

now THAT was an intelligent answer from someone who clearly understands
every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!

thank you!



"Dave Anderson" <NYRUMTPELVWH@s pammotel.comwro te in message
news:O5Xqh741GH A.4312@TK2MSFTN GP02.phx.gbl...

Mike Brind wrote:

>A full and complete explanation of this was provided by Bob
>Barrows very early on in this thread, where he discusses the
>fact that you can't order by an alias in Jet SQL, so you need
>to order by the ordinal position of the calculated column.

>
I generally try to stay out of Access threads, but I have been following
this one, and I have to ask why anyone even bothers to create a column for
the random value. It is not required.
>
Working from the tried & true SQL Server favorite...
>
SELECT ... ORDER BY NEWID()
>
...I wondered if the same would work in Access. So I opened a connection
to the Northwind database and tried:
>
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(EmployeeID* " + -Math.random() + ")"
)
>
This is obviously in JScript, but the principle is the same -- There is no
need to squabble over the column number, since we do not return the random
number in a column.
>
This particular example is a poor random generator -- I ran this 10,000
times and got a distribution like this:
>
Record 1 : 100
Record 2 : 122
Record 3 : 1526
Record 4 : 1584
Record 5 : 1302
Record 6 : 1412
Record 7 : 1232
Record 8 : 1487
Record 9 : 1235
>
When normalized so the highest value is 100, this has a standard deviation
of 36.64. Not good. But when I added another Rnd to the mix, the
distribution got flatter:
>
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(Rnd(Employe eID*" + -Math.random() + "))"
)
>
Record 1 : 1462
Record 2 : 1389
Record 3 : 1115
Record 4 : 937
Record 5 : 982
Record 6 : 1027
Record 7 : 1094
Record 8 : 996
Record 9 : 998
>
Normalized standard deviation: 12.81. I ran this test a couple of times,
and noticed a slight bias toward record 1, so I jacked it up to 50,000
(this required me to pump up Server.ScriptTi meout), and got this:
>
7504
7282
5350
4723
4818
5329
5158
4825
5011
>
(Std dev: 14.21). There definitely seems to be a bias toward the lowest
values. I made one last adjustment (sign change on inner Rnd)...
>
RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(-Rnd(EmployeeID* " + -Math.random() + "))"
)
>
...and ran it 180,000 times. This yielded the following distribution:
>
20529
22977
20013
17616
19926
21250
20085
17665
19939
>
This is much better, with normalized standard deviation of 7.19. Probably
random enough for most needs. My conclusion? Jimmy could get by with this:
>
Randomize()
Set oRS=oConn.Execu te("SELECT TOP 1 EMAIL_ADDRESS FROM TABLE1 ORDER BY
Rnd(-Rnd(ID*" & -Rnd & "))")
>
>
>
--
Dave Anderson
>
Unsolicited commercial email will be read at a cost of $500 per message.
Use of this email address implies consent to these terms.
>

[Mike Brind]

Re: random record with SELECT TOP does NOT work


Jimmy wrote:

now THAT was an intelligent answer from someone who clearly understands
every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!
>

And after THAT comment, let's just see how much help you get in future.

--
Mike Brind

[Dave Anderson]

Re: random record with SELECT TOP does NOT work

Jimmy wrote:

now THAT was an intelligent answer from someone who clearly
understands every piece of the SQL string he provided as an
example.

Only if you classify "I don't know much, but this is what I observed" as
intelligent.

I need to point out the fact that before this thread, I had never used
Access (unless you count exporting into SQL Server), so there was much about
it that I did not understand. For example, I had no idea that you could call
VBA functions inside your SQL statement. But then I saw all the examples
with Rnd(), which I could not find in the Access SQL reference, so I went
digging. The only reference to Rnd() was in the VBA function list, so I read
its documentation
(http://msdn.microsoft.com/library/en...l/vafctRnd.asp) and
drew more of my conclusions from that, such as the notion that I should
change the sign on the inner Rnd.

In short, I knew about as much about your desired task as you did. I don't
work in Access, I don't use VBScript for ASP unless forced to -- I am a
virtual novice in each. But I used my intellectual curiosity to overcome
this. And I can't understand why you did not do so yourself. If you want to
know if something works, try it. Don't ask us if it will work. When you come
across a problem, SIMPLIFY to the smallest case that will duplicate that
problem. These are fundamental debugging skills. And there is one more that
I find most important of all: http://en.wikipedia.org/wiki/RTFM.

all of the early posters to this topic were simply pasting
something they found on a website, not understanding
exactly what it was doing, yet still having the nerve to
have a holier than thou attitude!

Count me among them, then. It is a rare article on aspfaq.com that contains
non-functioning examples. When someone claims he tried the example from
Aaron's site and it "doesn't work", I assume he has not implemented it
properly. There are dozens of reasons for this assumption, not least of
which is the newbie signature "it doesn't work".

Oh, and how was "simply pasting something they found on a website, not
understanding exactly what it was doing" any different from what you were
doing? There were real answers provided in many of those responses, but I
don't think you saw past the fact that pasting them into your script
verbatim did not produce your desired result.

I do not post here in order to do your work for you. I helped you because
you did not understand a concept. My goal was not for you to solve your
problem, but rather for you to understand how to solve the problem yourself.
I truly believe that most of the contributors in this thread share that
goal.

You would be well served to calm down and re-read the thread to see what
else you might pick up. Leave the ad hominem stuff out of this forum.



--
Dave Anderson

Unsolicited commercial email will be read at a cost of $500 per message. Use
of this email address implies consent to these terms.

[Aaron Bertrand [SQL Server MVP]]

Re: random record with SELECT TOP does NOT work

now THAT was an intelligent answer from someone who clearly understands

every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!

Right, we were the ones not understanding what it was doing.

Something I should have done about 50 messages ago:

*PLONK*

Good luck with your next moronic thread here.

[Larry Bud]

Re: random record with SELECT TOP does NOT work

>
>
>
"Dave Anderson" <NYRUMTPELVWH@s pammotel.comwro te in message
news:O5Xqh741GH A.4312@TK2MSFTN GP02.phx.gbl...

Mike Brind wrote:

A full and complete explanation of this was provided by Bob
Barrows very early on in this thread, where he discusses the
fact that you can't order by an alias in Jet SQL, so you need
to order by the ordinal position of the calculated column.

I generally try to stay out of Access threads, but I have been following
this one, and I have to ask why anyone even bothers to create a column for
the random value. It is not required.

Working from the tried & true SQL Server favorite...

SELECT ... ORDER BY NEWID()

...I wondered if the same would work in Access. So I opened a connection
to the Northwind database and tried:

RS = CN.Execute(
"SELECT TOP 1 * FROM Employees " +
"ORDER BY Rnd(EmployeeID* " + -Math.random() + ")"
)

This is obviously in JScript, but the principle is the same -- There is no
need to squabble over the column number, since we do not return the random
number in a column.

This particular example is a poor random generator -- I ran this 10,000
times and got a distribution like this:

Record 1 : 100
Record 2 : 122
Record 3 : 1526
Record 4 : 1584
Record 5 : 1302
Record 6 : 1412
Record 7 : 1232
Record 8 : 1487
Record 9 : 1235

Jimmy wrote:

now THAT was an intelligent answer from someone who clearly understands
every piece of the SQL string he provided as an example.
all of the early posters to this topic were simply pasting something they
found on a website, not understanding exactly what it was doing, yet still
having the nerve to have a holier than thou attitude!
>
thank you!

You're unwelcome, jackass.