Solved

How come this random query doesn't work?

Posted on 2003-11-06
8
332 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
AWS Advice on using WHM/cPanel 1 89
Website URL redirection 10 80
whm high memory usage in processes 7 121
Soundcloud.com 4 83
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 …
When it comes to showing a 404 error page to your visitors, you do not want that generic page to show, and you especially do not want your hosting provider’s ad error page to show either. In this article, I will show you how to enable the custom 40…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

749 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