469,658 Members | 1,862 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,658 developers. It's quick & easy.

some clarification

Hi there,

I was searching the net for some guidance in putting together a query that
would select random records from the main table. I came across this and it
works like a charm.

SELECT TOP 5 *
FROM tablename
ORDER BY Rnd(IsNull(fieldname)*0+1);

I understand the query up until the use of the Rnd part. I have been doing
my best to try and get a better grasp of SQL and was hoping someone could
offer some clarification on just how this query does what it does. As I have
said, I understand that the query is selecting the top five records that are
in the table, and that the table records are randomized by the Rnd function.
My ignorance comes into trying to get my head around the
"Rnd(IsNull(fieldname)*0+1)".

Any clarity would be greatly appreciated.

thanks
Nov 13 '05 #1
4 1635
The expression you are struggling with really has no meaning. It is a
circumlocution to fool the query optimizer into providing a different value
for each record.

If you use:
ORDER BY Rnd();
the query optimizer is clever enough to realize that the function value does
not depend on any field value, so it does not bother actually calling the
function again for every record. You end up with the same value in every
row, so there is no random selection.

To coax the optimizer to call the function on every row, you could pass the
primary key value into the function, e.g.:
ORDER BY Rnd([OrderID]);
Rnd() does not actually do anything with the argument, but the optimizer
does call the function for every record, and so you do get your random
values.

The example you posted looks like an attempt to create a numeric value that
involves a field name so the query optimizer calls the function for each
record.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"chanchito_cojones" <ch**********@hotmail.com> wrote in message
news:8o********************@rogers.com...
Hi there,

I was searching the net for some guidance in putting together a query that
would select random records from the main table. I came across this and it
works like a charm.

SELECT TOP 5 *
FROM tablename
ORDER BY Rnd(IsNull(fieldname)*0+1);

I understand the query up until the use of the Rnd part. I have been doing
my best to try and get a better grasp of SQL and was hoping someone could
offer some clarification on just how this query does what it does. As I
have said, I understand that the query is selecting the top five records
that are in the table, and that the table records are randomized by the
Rnd function. My ignorance comes into trying to get my head around the
"Rnd(IsNull(fieldname)*0+1)".

Any clarity would be greatly appreciated.

thanks

Nov 13 '05 #2
I *think* that if the query had included a simple call to the Rnd()
function, then it would have been evaluated ONCE for all records, thus
there would be no randomness at all. By including an arbitrary
fieldname in the expressiong, Access is forced to re-evaluate the
function for every record, thus ensuring that each record gets a
random number.

On Sun, 12 Dec 2004 21:24:31 -0500, "chanchito_cojones"
<ch**********@hotmail.com> wrote:
Hi there,

I was searching the net for some guidance in putting together a query that
would select random records from the main table. I came across this and it
works like a charm.

SELECT TOP 5 *
FROM tablename
ORDER BY Rnd(IsNull(fieldname)*0+1);

I understand the query up until the use of the Rnd part. I have been doing
my best to try and get a better grasp of SQL and was hoping someone could
offer some clarification on just how this query does what it does. As I have
said, I understand that the query is selecting the top five records that are
in the table, and that the table records are randomized by the Rnd function.
My ignorance comes into trying to get my head around the
"Rnd(IsNull(fieldname)*0+1)".

Any clarity would be greatly appreciated.

thanks

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security
Nov 13 '05 #3
Thanks for you help Allen. I understand how the funciton works now, and I
also tried your other suggestion which works just as well.

Thanks again.
Nov 13 '05 #4
Thanks Jack. I appreciate the clarity. =)

cheers
"Jack MacDonald" <ja**************@telus.net> wrote in message
news:eo********************************@4ax.com...
I *think* that if the query had included a simple call to the Rnd()
function, then it would have been evaluated ONCE for all records, thus
there would be no randomness at all. By including an arbitrary
fieldname in the expressiong, Access is forced to re-evaluate the
function for every record, thus ensuring that each record gets a
random number.

On Sun, 12 Dec 2004 21:24:31 -0500, "chanchito_cojones"
<ch**********@hotmail.com> wrote:
Hi there,

I was searching the net for some guidance in putting together a query that
would select random records from the main table. I came across this and it
works like a charm.

SELECT TOP 5 *
FROM tablename
ORDER BY Rnd(IsNull(fieldname)*0+1);

I understand the query up until the use of the Rnd part. I have been doing
my best to try and get a better grasp of SQL and was hoping someone could
offer some clarification on just how this query does what it does. As I
have
said, I understand that the query is selecting the top five records that
are
in the table, and that the table records are randomized by the Rnd
function.
My ignorance comes into trying to get my head around the
"Rnd(IsNull(fieldname)*0+1)".

Any clarity would be greatly appreciated.

thanks

**********************
ja**************@telusTELUS.net
remove uppercase letters for true email
http://www.geocities.com/jacksonmacd/ for info on MS Access security

Nov 13 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by Shea Martin | last post: by
3 posts views Thread by John D. Sanders | last post: by
2 posts views Thread by Ethan | last post: by
3 posts views Thread by solomon_13000 | last post: by
reply views Thread by chanchito_cojones | last post: by
70 posts views Thread by rahul8143 | last post: by
3 posts views Thread by Jon Booth | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.