9 Replies Latest reply on Jun 28, 2009 9:27 PM by Dileep_NR

    cfquery , a random selection

    plarts Level 1

      How can I get a random record from an SQL select ?


      I tried :

      <cfquery .....>

      select my_id from mytable

      order by rand()



      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,


        • 1. Re: cfquery , a random selection
          -==cfSearching==- Level 4

          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:



          • 2. Re: cfquery , a random selection
            plarts Level 1

            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,



            • 3. Re: cfquery , a random selection
              -==cfSearching==- Level 4

              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 ;-)

              • 4. Re: cfquery , a random selection
                plarts Level 1

                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.


                • 5. Re: cfquery , a random selection
                  Dan Bracuk Level 5

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

                  • 6. Re: cfquery , a random selection
                    plarts Level 1

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

                    Thanks, Pierre.

                    • 7. Re: cfquery , a random selection
                      craigkaminsky Level 3

                      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)



                      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.

                      • 8. Re: cfquery , a random selection


                        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
                                      <!--- 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 --->
                            <!--- 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 --->
                              <cfset st.currentPos = 1>


                          <!--- 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())>


                        <!--- 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>

                        <!--- Now that we have chosen the film to "Feature", --->
                        <!--- Get all important info about it from database. --->
                        <cfquery name="GetFilm" datasource="#REQUEST.dataSource#">
                        MovieTitle, Summary, Rating,
                        AmountBudgeted, DateInTheaters
                        FROM Films f, FilmsRatings r
                        WHERE FilmID = #thisMovieID#
                        AND f.RatingID = r.RatingID

                        <!--- Now Display Our Featured Movie --->
                        <!--- Define formatting for our "feature" display --->
                        <style type="text/css">
                        TH.fm { background:RoyalBlue;color:white;text-align:left;
                        TD.fm { background:LightSteelBlue;

                        <!--- Show info about featured movie in HTML Table --->
                        <table width="150" align="right" border="0" cellspacing="0">
                        <tr><th class="fm">
                        Featured Film
                        <!--- Movie Title, Summary, Rating --->
                        <tr><td class="fm">
                        <p align="right">Rated: #getFilm.Rating#</p>
                        <!--- 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>
                        <br clear="all">

                        • 9. Re: cfquery , a random selection
                          Dileep_NR Level 2



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



                          select * from mytable  order by newid()