473,322 Members | 1,431 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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 1834
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Shea Martin | last post by:
Which of the following do I use delete instead of just delete. //1.) // not sure about this one, as char is of size 1 char *str = new char; //2.) //not sure about this one, as it is a...
3
by: John D. Sanders | last post by:
I have just upgraded MySQL from version 3.23 to version 4.1.8 and now I am getting the following error when I try to run a script that worked: install_driver(mysql) failed: Can't load...
2
by: Ethan | last post by:
This is a clarification of a previous message, which was not expressed very well. I have a set of checkboxes near the bottom of the page and a function that checks or unchecks all of them. But when...
3
by: solomon_13000 | last post by:
> Wonthaggi Civic Theatre 'WCT' Case Study > > The town of Wonthaggi has a theatre which is owned and > operated by the local council, it is called the > Wonthaggi Civic Theatre (WCT) and a wide...
0
by: chanchito_cojones | last post by:
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...
70
by: rahul8143 | last post by:
hello, 1) First how following program get executed i mean how output is printed and also why following program gives different output in Turbo C++ compiler and Visual c++ 6 compiler? void main()...
1
by: Ken | last post by:
Hello Everyone, I would like to use EnumPorts twice on my form. The first time I want to use Port_Info_1 to just list the Port Names. The second instance where I need EnumPorts , is when I...
8
by: Sai Kit Tong | last post by:
In the article, the description for "Modiy DLL That Contains Consumers That Use Managed Code and DLL Exports or Managed Entry Points" suggests the creation of the class ManagedWrapper. If I...
3
by: Jon Booth | last post by:
I have just started programming .NET having come from ASP. We used to create a connection and store it in a session.This meant for each user logging into the site there was only one connection ...
1
by: CrispinH | last post by:
I was after clarification of some of serialization issues. I think I know the answers, but I want to be sure. 1. In a serializable collection class is it mandatory (for serialization) to have...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.