Solved

How come this random query doesn't work?

Posted on 2003-11-06
8
330 Views
Last Modified: 2013-12-24
<CFQUERY name="q_GetImages" datasource="Rating">
  SELECT TOP 1 *
  FROM ImageIndex II
  WHERE II.Approved = 1 AND NOT EXISTS (SELECT 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
  ORDER BY rnd([ImageID])
 </CFQUERY>

For some reason this isn't returning random records, why not?
0
Comment
Question by:Max_Davis
8 Comments
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9698656
Is this line correct ?  
   WHERE II.Approved = 1 AND NOT EXISTS (SELECT 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)

If not change it with this one, if this is what you are referring :
   WHERE II.Approved = 1 AND NOT EXISTS (SELECT TOP 1 FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)

Goodluck!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9698666
Ooops! Sorry, I forgot something.

It should be this way :

WHERE II.Approved = 1 AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)

Regards!
eNTRANCE2002 :-)
0
 
LVL 14

Expert Comment

by:Renante Entera
ID: 9698701
Try this query:

<CFQUERY name="q_GetImages" datasource="Rating">
  SELECT TOP 1 *
  FROM ImageIndex II
  WHERE II.Approved = 1
  AND NOT EXISTS (SELECT TOP 1 * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
  ORDER BY rnd([RT.ImageID])
</CFQUERY>

Perhaps this will return any records.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 17

Expert Comment

by:anandkp
ID: 9698957
<CFQUERY name="q_GetImages" datasource="Rating">
  SELECT TOP 1 *
  FROM ImageIndex II
  WHERE II.Approved = 1 AND NOT EXISTS (SELECT * FROM RatingsTable RT WHERE RT.ImageId = II.ImageId)
  ORDER BY rnd([II.ImageID])
 </CFQUERY>
0
 
LVL 10

Expert Comment

by:substand
ID: 9699314
it's not returning random results because even though the sql spec calls for it to, two things might be happening:

1) your dbms probably just returns the first record in the db file
2) if the dbms is set up to actually find a random record instead of returning the first one, you have an index set up on one or more fields so it is returning based on the index.
0
 
LVL 1

Accepted Solution

by:
Wasistdas earned 400 total points
ID: 9699614
Why not be simpler?

<CFQUERY name="q_GetImages" datasource="Rating">
  SELECT *
  FROM ImageIndex
</cfquery>
<cfif q_GetImages.recordcount>
<cfset random_factor=RandRange(1,q_GetImages.recordcount)>
<cfoutput query="q_GetImages" startrow="#random_factor#" maxrows="1">
[output]
</cfoutput>
</cfif>
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This is a guide to setting up a new WHM/cPanel Server to be used for web hosting accounts. It is intended for web hosting company administrators and dedicated server owners. For under $99 per month (considering normal rate of Big Data Cetnters like …
In our day to day coding, how many times have we come across a necessity to check whether a URL is a broken link or not? For those of you that answered countless and are using ColdFusion like myself, then this article is for you.  It will show yo…
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question