Solved

How come this random query doesn't work?

Posted on 2003-11-06
8
331 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
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

Easy, flexible multimedia distribution & control

Coming soon!  Ideal for large-scale A/V applications, ATEN's VM3200 Modular Matrix Switch is an all-in-one solution that simplifies video wall integration. Easily customize display layouts to see what you want, how you want it in 4k.

Question has a verified solution.

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

Most ColdFusion developers get confused between the CFSet, Duplicate, and Structcopy methods of copying a Structure, especially which one to use when. This Article will explain the differences in the approaches with examples; therefore, after readin…
One of the typical problems I have experienced is when you have to move a web server from one hosting site to another. You normally prepare all on the new host, transfer the site, change DNS and cross your fingers hoping all will be ok on new server…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

837 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