• Global community
    • Language:
      • Deutsch
      • English
      • Español
      • Français
      • Português
  • 日本語コミュニティ
    Dedicated community for Japanese speakers
  • 한국 커뮤니티
    Dedicated community for Korean speakers
Exit
0

cfquery , a random selection

Contributor ,
Jun 27, 2009 Jun 27, 2009

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

TOPICS
Advanced techniques

Views

2.9K

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jun 27, 2009 Jun 27, 2009

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

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 27, 2009 Jun 27, 2009

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Valorous Hero ,
Jun 27, 2009 Jun 27, 2009

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 😉

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 28, 2009 Jun 28, 2009

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jun 28, 2009 Jun 28, 2009

Copy link to clipboard

Copied

Hope the table doesn't have very many records in it.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 28, 2009 Jun 28, 2009

Copy link to clipboard

Copied

No, there is a few records, will not grow.

Thanks, Pierre.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jun 28, 2009 Jun 28, 2009

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.

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Guest
Jun 28, 2009 Jun 28, 2009

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>

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Contributor ,
Jun 28, 2009 Jun 28, 2009

Copy link to clipboard

Copied

LATEST

Hi,

Please try "order by newid()" with the select

select * from mytable  order by newid()

Votes

Translate

Translate

Report

Report
Community guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Resources
Documentation