random record with SELECT TOP does NOT work

[Mike Brind]

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.

 

[Aaron Bertrand [SQL Server MVP]]

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]

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.

 

[SEVEN]

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]

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.

 

[Dave Anderson]

Working from the tried & true SQL Server favorite...
[snip]

...My conclusion? Jimmy could get by with this:
Randomize()
Set oRS=oConn.Execute("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("SELECT Count(*) AS RecordCount FROM Orders")
rc = RS.Fields("RecordCount").Value

Step 2: Use top 1 DESC from top N ASC to get your row
RS = CN.Execute("SELECT TOP 1 * FROM Orders WHERE OrderID IN (SELECT TOP
" + Math.ceil(rc*Math.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?

 

[Larry Bud]

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.