Copy link to clipboard
Copied
How can I get a random record from an SQL select ?
I tried :
<cfquery .....>
select my_id from mytable
order by rand()
</cfquery>
rand() is not accepted.
I want to return a random record from a table, that's all.
from a simple SQL query.
Thanks for any answer,
Pierre
Copy link to clipboard
Copied
With database questions it is always a good idea to mention your database type up front. In answer to your quqestion, it is possible with most databases. But the syntax is database dependent:
http://www.petefreitag.com/item/466.cfm
Copy link to clipboard
Copied
You are right, this case is under an Access database.
Thanks for the page with different syntax for different database,
nothing for Access.
If you know the equivalent for Access (if it exists), thanks,
Pierre.
Copy link to clipboard
Copied
I do not really use Access. So I cannot help you there. Google ColdFusion + Access + random + record or something similar. I am certain you are not the first person (or even 1000th) to do this 😉
Copy link to clipboard
Copied
Thanks, I did not find anything for Access, not sure this exists.
Then I used ColdFusion,
with a query on all records, find a random number between 1 and the recordcount,
then loop with a startrow and endrow on that random number.
This works fine.
But it would be faster directly inside the DB, I suppose.
Pierre.
Copy link to clipboard
Copied
Hope the table doesn't have very many records in it.
Copy link to clipboard
Copied
No, there is a few records, will not grow.
Thanks, Pierre.
Copy link to clipboard
Copied
I no longer have Access DBs on which to test but I believe this is all you would need to grab a random record from Access:
<cfquery name="myquery" datasource="dsn">
SELECT TOP 1 field_1, field_2, field_3
FROM the_table ORDER BY Rnd(field_1)</cfquery>
The Rand() function you mentioned in the OP is part of MySQL implementation of the SQL standard. So, MySQL would be ORDER BY Rand(). SQL Server 2005 would be ORDER BY NEWID(). And Access, I believe, is Rnd(field).
Hopefully the above query structure gets you what you need.
Copy link to clipboard
Copied
Hi
I would like to use the demistration by Ben Forta, to explain to you how to do that. This application is made by NATE WEISS, for educational purposes. now what this do is, it sets up a variable (client var) with a position to identify which add (in this example) to display. So if you are at lets say add 3, your code now knows that it should move on to the next featured movie, and it wont display the same one. Here is the code, I hope it helps:
<!---
Filename: FeaturedMovie.cfm
Created by: Nate Weiss (NMW)
Purpose: Displays a single movie on the page, on a rotating basis
Please Note Application variables must be enabled
--->
<!--- We want to obtain an exclusive lock if this --->
<!--- is the first time this template has executed, --->
<!--- or the time for this featured movie has expired --->
<cfif (not isDefined("APPLICATION.movieRotation"))
or (dateCompare(APPLICATION.movieRotation.currentUntil, now()) eq -1)>
<!--- Make sure all requests wait for this block --->
<!--- to finish before displaying the featured movie --->
<cflock scope="APPLICATION" type="Exclusive" timeout="10">
<!--- If this is the first time the template has executed... --->
<cfif not isDefined("APPLICATION.movieRotation")>
<!--- Get all current FilmIDs from the database --->
<cfquery name="GetFilmIDs" datasource="#REQUEST.dataSource#">
SELECT FilmID FROM Films
ORDER BY MovieTitle
</cfquery>
<!--- Create structure for rotating featured movies --->
<cfset st = structNew()>
<cfset st.movieList = valueList(getFilmIDs.FilmID)>
<cfset st.currentPos = 1>
<!--- Place structure into APPLICATION scope --->
<cfset APPLICATION.movieRotation = st>
<!--- ...otherwise, the time for the featured movie has expired --->
<cfelse>
<!--- Shorthand name for structure in application scope --->
<cfset st = APPLICATION.movieRotation>
<!--- If we havenÕt gotten to the last movie yet --->
<cfif st.currentPos lt listLen(st.movieList)>
<cfset st.currentPos = st.currentPos + 1>
<!--- if already at last movie, start over at beginning --->
<cfelse>
<cfset st.currentPos = 1>
</cfif>
</cfif>
<!--- In any case, choose the movie at the current position in list --->
<cfset st.currentMovie = listGetAt(st.movieList, st.currentPos)>
<!--- This featured movie should "expire" a short time from now --->
<cfset st.currentUntil = dateAdd("s", 5, now())>
</cflock>
</cfif>
<!--- Use a ReadOnly lock to grab current movie from application scope... --->
<!--- If the exclusive block above is current executing in another thread, --->
<!--- then ColdFusion will ÔwaitÕ before executing the code in this block. --->
<cflock scope="APPLICATION" type="ReadOnly" timeout="10">
<cfset thisMovieID = APPLICATION.movieRotation.currentMovie>
</cflock>
<!--- Now that we have chosen the film to "Feature", --->
<!--- Get all important info about it from database. --->
<cfquery name="GetFilm" datasource="#REQUEST.dataSource#">
SELECT
MovieTitle, Summary, Rating,
AmountBudgeted, DateInTheaters
FROM Films f, FilmsRatings r
WHERE FilmID = #thisMovieID#
AND f.RatingID = r.RatingID
</cfquery>
<!--- Now Display Our Featured Movie --->
<cfoutput>
<!--- Define formatting for our "feature" display --->
<style type="text/css">
TH.fm { background:RoyalBlue;color:white;text-align:left;
font-family:sans-serif;font-size:10px}
TD.fm { background:LightSteelBlue;
font-family:sans-serif;font-size:12px}
</style>
<!--- Show info about featured movie in HTML Table --->
<table width="150" align="right" border="0" cellspacing="0">
<tr><th class="fm">
Featured Film
</th></tr>
<!--- Movie Title, Summary, Rating --->
<tr><td class="fm">
<b>#getFilm.MovieTitle#</b><br>
#getFilm.Summary#<br>
<p align="right">Rated: #getFilm.Rating#</p>
</td></tr>
<!--- Cost (rounded to millions), release date --->
<tr><th class="fm">
Production Cost $#round(val(getFilm.AmountBudgeted) / 1000000)# Million<br>
In Theaters #dateFormat(getFilm.DateInTheaters, "mmmm d")#<br>
</th></tr>
</table>
<br clear="all">
</cfoutput>
Copy link to clipboard
Copied
Hi,
Please try "order by newid()" with the select
select * from mytable order by newid()