Will this work to pull a Random record?

[brian1313]

I'm trying to display a single random record from a recordset. Will this code work? I'm at work right now, and can't test it out.

<!--- BEGIN EXMAPLE --->

<cfquery name=&quot;random&quot; datasource=&quot;datasource&quot;>
SELECT * FROM table WHERE random = True
</cfquery>

<cfset rec=Int(Random.RecordCount * Rand())>
<cfif rec EQ 0>
<cfset rec=1>
</cfif>

<cfoutput query=&quot;random&quot; maxrows=&quot;1&quot; startrow=&quot;#rec#&quot;>
output fields and whatever
</cfoutput>

<!--- END EXMAPLE --->


Well, that's what I've got right now. If anyone could let me know if that will work or if there is

a better way to do it, I'd really appreciate it.

Thanks in advance...

-b-

 

[SidianConsulting]

If you are using SQL Server 2000 you could do it this way:

SELECT TOP 1 myfield
FROM mytable
ORDER BY NEWID()

This would return one random record &quot;myfield&quot; from the table &quot;mytable&quot;. You can SELECT * or TOP 5, TOP 10, etc., based on how many records you need randomized.

-Tek

 

[brian1313]

I'm just using access. So will this work?

I don't think there would any problems.

-b-

 

[r937]

brian, your method is fine, except you are bringing in the entire table

if the table has incremental ids (like an autunumber), it would be better to replace your first query with

SELECT count(*) as howmany FROM table

and then use howmany instead of RecordCount to create the random number, then instead of your startrow cfoutput, run a query to get the desired record using

where ID <= rec

rudy

 

[brian1313]

I think I get it. How's this?

<!--- BEGIN EXAMPLE --->

<cfquery name=&quot;get_random&quot; datasource=&quot;datasource&quot;>
SELECT count(*) as howmany FROM table WHERE random = True
</cfquery>

<cfset rec=Int(howmany * Rand())>

<!-- WOULD I STILL NEED THIS TO PREVENT ZERO FROM BEING RETURNED? -->

<cfif rec EQ 0>
<cfset rec=1>
</cfif>

<cfquery name=&quot;return_record&quot; datasource=&quot;datasource&quot;>
SELECT * as howmany FROM table WHERE ID <= rec AND random = True
</cfquery>

<cfoutput query=&quot;return_record&quot; maxrows=&quot;1&quot;>
output data
</cfoutput>

<!--- END EXAMPLE --->


I only have the one question about keeping the CFIF. If I'm selecting <= rec, it would just select the very first possible row and I could pull out the CFIF statement, right?

-b-