Get a random record from a db

[gtbikerider]

I'm trying to select a random record from a database. The following vaguely works, but it selects a random number between 1 & 20 (say), and if that number is 12 there's no guarantee there is a record #12. This is obviously a really bad way to do this.

*******************************************

<!--- find out how many records there are in the db --->
<cfquery name=&quot;GetRecords&quot; datasource=&quot;mydatabase&quot;>
select recordID
from table
</cfquery>

<!--- get a random number between 1 & number of records--->
<cfset random = RandRange(1,#GetRecords.RecordCount#)>

<!--- get the randomly selected record from the db --->
<cfquery name=&quot;GetRecord&quot; datasource=&quot;mydatabase&quot;>
select record
from table
where RecordID LIKE #random#
</cfquery>

*******************************************

I've got as far as figuring out I should be using a list (correct me if I'm wrong) but I can't figure the code out. Anyone assist? --
John

 

[Extras]

Hello

My suggestion is to try out the CF_RGrabber custom tag from the MM site. I have found it to be a very versatile &quot;random-delivery&quot; tag. You can connect it to a query or a list and therefore eliminate the complication of delivering a non-existent random value.

http://devex.macromedia.com/developer/gallery/SearchResults.cfm?keywords=RGrabber&x=12&y=8

If the above link does not work, just search for CF_RGrabber in the link below:

http://devex.macromedia.com/developer/gallery/

 

[gtbikerider]

Thanks, it helps a little. This returns a #randomlist# But having got the list of random numbers I'm not sure how I can use them (being unfamiliar with list). What I'd need to be able to do is...

<cfquery name=&quot;GetRecord&quot; datasource=&quot;mydatabase&quot;>
select record
from table
where RecordID LIKE #anything_in_randomlist#
</cfquery>

Achievable? --
John

 

[Sarky78]

yes something like that is possible you have to change your query.

<cfquery name=&quot;GetRecord&quot; datasource=&quot;mydatabase&quot;>
select record
from table
where RecordID IN (#randomList#)
</cfquery>

this will then return everything from the database where it matches a value in the randomlist

so if your randomlist has values 1,4,2,7,6 in it then it will return all of the RecordID's from the db where there is a match !