Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 342
  • Last Modified:

How come this random query doesn't work?

<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
Max_Davis
Asked:
Max_Davis
1 Solution
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
 
Renante EnteraSenior PHP DeveloperCommented:
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
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
anandkpCommented:
<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
 
substandCommented:
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
 
WasistdasCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now