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