Selecting Randoms from Query of Queries

[DixieGal]

I can create a query that selects random records
<cfquery....>
select top 10 * from table order by newid() <!--- SQL Server --->
</cfquery>

or
<cfquery....>
select top 10 * from table order by rand() limit 10 <!--- MySQL--->
</cfquery>

However, is there a way to cache a query then select random records from it?
I want to cache the overall query that retrieves all the records, then
randomly pull
from that cached query.

If I use newid() or rand(), the query bombs on the parentheses.

Any suggestions?

[DixieGal]

well, it's not really a query of queries... more like a query of a cached
query :)

"DixieGal" <***@ospammers-honeyhousedesigns.com> wrote in message news:ffsn1b$fag$***@forums.macromedia.com...
|I can create a query that selects random records
| <cfquery....>
| select top 10 * from table order by newid() <!--- SQL Server --->
| </cfquery>
|
| or
| <cfquery....>
| select top 10 * from table order by rand() limit 10 <!--- MySQL--->
| </cfquery>
|
| However, is there a way to cache a query then select random records from
it?
| I want to cache the overall query that retrieves all the records, then
| randomly pull
| from that cached query.
|
| If I use newid() or rand(), the query bombs on the parentheses.
|
| Any suggestions?
|
| --
|
| Tami
|
|

[n***@gmail.com]

The easiest solution to this would be that since you can't randomize
the query, create another row in your query with a random number and
then sort by that row.

This is an example of something that I wrote that uses this... in the
example, an XML file is pulled in, parsed, made into a query, and
outputted.

Hope this helps!

-Steve




<!---Grab File For Reading In--->
<cfhttp method="get"
url="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.xml"
throwonerror="yes"></cfhttp>


<!---Make sure our XML file is okay for parsing, if not, display
error--->

<cfif #cfhttp.StatusCode# NEQ "200 OK">
Sorry, this data is currently unavailable. Please try again later.

<!--- Retrieve is OK... parse it and start displaying --->

<cfelse>
<cfset top5 = XMLparse(cfhttp.fileContent)>
<cfset entries = XMLSearch(top5, '/topfivelist/entry')>
<cfset top5query = queryNew("title, link, description, randnum")>
<!--- NOTE: SEE THE QUERY ROW LABELED "randnum" --->
<cfset n = arrayLen(entries)>
<cfloop from="1" to="#n#" index="i">
<cfset queryAddRow(top5query)>
<cfset querySetCell(top5query, "title",
entries[i].title.xmlText,i)>
<cfset querySetCell(top5query, "link",
entries[i].link.xmlText,i)>
<cfset querySetCell(top5query, "description",
entries[i].description.xmlText,i)>
<cfset querySetCell(top5query, "randnum",
"#RandRange(1,1000)#")> <!--- NOTE: THIS IS WHERE OUR RANDOM NUMBER IS
GENERATED --->
</cfloop>


<!--- Our output query... sorted by the randnum field to make the
query random --->

<cfquery name="selection" dbtype="query" maxrows="3">
SELECT * FROM top5query ORDER BY randnum ASC
</cfquery>


<!--- Output our results --->
<cfoutput query="selection">
<h2>#selection.title#</h2>
#selection.description#<br>
<a href="XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX.cfm">View
More!</a><br>
<br>
</cfoutput>

</cfif>

Post by DixieGal
well, it's not really a query of queries... more like a query of a cached
query :)
|I can create a query that selects random records
| <cfquery....>
| select top 10 * from table order by newid() <!--- SQL Server --->
| </cfquery>
|
| or
| <cfquery....>
| select top 10 * from table order by rand() limit 10 <!--- MySQL--->
| </cfquery>
|
| However, is there a way to cache a query then select random records from
it?
| I want to cache the overall query that retrieves all the records, then
| randomly pull
| from that cached query.
|
| If I use newid() or rand(), the query bombs on the parentheses.
|
| Any suggestions?
|
| --
|
| Tami
|
|