[thelist] CF - Random Rows?

[Rudy_Limeback at maritimelife.ca]

> Now, as I've got it set up (see below) I'm getting that there 
> are too few parameters. Why isn't Int(GetExisting.ID) seen 
> as a legitimate int? Does anyone know the work around?

 
hi frank

okay, i can see what you're trying to do but you don't have to get *all* 
the IDs from the database just to get the lowest and the highest -- you 
can get those directly from the database

   <cfquery name="GetMinMax" datasource="#datasource#">
      SELECT MIN(ID) as MinID
           , MAX(ID) as MaxID
        FROM Address
   </cfquery>

<tip>
whenever an aggregate function like MIN() or MAX() is used without a GROUP 
BY clause, the entire result set is the (implicit) group
</tip>

now use the cf function RandRange to get a random number which falls 
between the low and high, and go get that record

   <cfquery name="GetRandomRecord" datasource="#datasource#">
      SELECT [stuff]
        FROM Address
       WHERE ID = #RandRange(GetMinMax.MinID,GetMinMax.MaxID)#
   </cfquery>

this works fine only if there are no gaps in your ID numbers

however, as you mentioned,

> I'm trying to create the situation whereby I can access a 
> random record from the lot. I had originally used max and min, 
> but if a record is deleted from the set, and the random number 
> turns out to be the ID of the deleted record, there's a problem.

you can still use RandRange to come up with a random ID number in between 
the min and the max, but now the trick is, if the record with the selected 
ID is gone, all you have to do is pick a record that is "close by" 

simple concept, no?   so's the sql, really, once you've seen it

think of the random number as dividing the table into two parts

in the lower part of the table are all the records with an ID that is 
*less than or equal to* the random number

in the upper part, all the records have an ID that is *greater than or 
equal to* the random number

to get a "close" record, you can pick either the highest one out of the 
lower half, or the lowest one out of the upper half

   <cfquery name="GetRandomRecord" datasource="#datasource#">
      SELECT [stuff]
        FROM Address
       WHERE ID = 
         ( SELECT MIN(ID)
             FROM Address
            WHERE ID >= 
              #RandRange(GetMinMax.MinID,GetMinMax.MaxID)# )
   </cfquery>

let me know if this still doesn't make sense


rudy limeback
r937.com
evolt.org