random record with SELECT TOP does NOT work

[SEVEN]

Re: random record with SELECT TOP does NOT work


"Larry Bud" <larrybud2002@y ahoo.comwrote in message
news:1158240460 .638752.239930@ i3g2000cwc.goog legroups.com...

>

>>
>>
>>
>"Dave Anderson" <NYRUMTPELVWH@s pammotel.comwro te in message
>news:O5Xqh741G HA.4312@TK2MSFT NGP02.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.
>

i wasnt thanking you, dumbass.

after all, youre the genius that said:

"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2

nice guess, but as you are now aware, that couldnt be more wrong (or stupid)

maybe find a new group to offer help in?

[Mike Brind]

Re: random record with SELECT TOP does NOT work


SEVEN wrote:

"Larry Bud" <larrybud2002@y ahoo.comwrote in message
news:1158240460 .638752.239930@ i3g2000cwc.goog legroups.com...

>
>
>
"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.

>
i wasnt thanking you, dumbass.
>
after all, youre the genius that said:
>
"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2
>
nice guess, but as you are now aware, that couldnt be more wrong (or stupid)
>
maybe find a new group to offer help in?

Larry's example worked perfectly. It did exactly what you wanted it to
do. The fact that you still don't get that reinforces just how stupid
you really are. You have demonstrated beyond any doubt whatsoever many
times that you are not capable of discerning what is right and wrong
here.

I suggest that you not only find another group to help you, but another
subject - one that is within your very obviously limited intellectual
capabilities. Either that or a therapy group.

What a loser.

--
Mike Brind

[Dave Anderson]

Re: random record with SELECT TOP does NOT work

I wrote:

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

[snip]

...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 & "))")

Just to put the final nail in this thread, I think it is fair to point out
that my analysis is thoroghly incomplete. I did no comparison between using
a column-based ordering and my solution, nor did I examine anything but a
simple example. My solution may be acceptible for a table with 9 records,
each with an identity under 10, but it made no attempt to do the same for a
larger table with sequence gaps and a wide range of identities.

To illustrate the danger of relying on limited analysis, I offer this
observation: When run against Northwind's [Orders] table (approximately 830
unique IDs), the distribution is horribly skewed toward a small handful of
values. Over a run of 10,000, I only hit about 700 values, with most in
single digits for frequency, but five values in excess of 100 -- one of
those values appearing 343 times!

Just to be sure this was not an aberration, I ran it again. These are the
top 10 values, ordered by frequency:

Run 1 Run 2
========= =========
10700 343 10700 355
10497 257 11005 224
11005 217 10497 223
10561 156 10561 167
10601 140 10601 133
10397 81 11016 85
11022 80 10397 83
11016 78 11022 82
11017 72 10843 66
10714 60 11017 66

Those are virtually the same values. Moreover, a flat distribution would
have yielded 830 values with roughly 12 appearances each. So my suggestion
is far from ideal for this data set.

There *is* a better way to get a random distribution. This one is as good as
your random number generator. Oddly enough, it turned in better performance
than my earlier suggestion. It is in JScript, so the distribution depends on
Math.random():

Step 1: Get a count
RS = CN.Execute("SEL ECT Count(*) AS RecordCount FROM Orders")
rc = RS.Fields("Reco rdCount").Value

Step 2: Use top 1 DESC from top N ASC to get your row
RS = CN.Execute("SEL ECT TOP 1 * FROM Orders WHERE OrderID IN (SELECT TOP
" + Math.ceil(rc*Ma th.random()) + " OrderID FROM Orders ORDER BY OrderID
ASC) ORDER BY OrderID DESC")

Run 50,000 times against [Orders], I got minimum frequency 35 and maximum
88. All 830 values were covered. Using VBScript and Rnd**, results were
similar, with frequencies ranging from 38 to 90.

I have no more to say on the topic. I think.


** What a pain in the posterior THAT was. It took me quite some time to
figure out that I needed to use CSng() on the recordcount to multiply
against Rnd without an overflow. Never mind that it took twice as many lines
of code. Who needs that nonsense?
--
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.

[Larry Bud]

Re: random record with SELECT TOP does NOT work


SEVEN wrote:

"Larry Bud" <larrybud2002@y ahoo.comwrote in message
news:1158240460 .638752.239930@ i3g2000cwc.goog legroups.com...

>
>
>
"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.

>
i wasnt thanking you, dumbass.
>
after all, youre the genius that said:
>
"heres the real answer:"
SELECT TOP 1 email_address, Rnd(ID) FROM mytable ORDER BY 2
>
nice guess, but as you are now aware, that couldnt be more wrong (or stupid)

Yep, my example works perfectly. The fact that you don't have the
ability to apply it to your situation doesn't say anything about me.

But believe me, I'll make us both happy and I won't hesitate to skip
over any other help you ask for in the future.