[thelist] CF - Random Rows?
Rudy_Limeback at maritimelife.ca
Rudy_Limeback at maritimelife.ca
Fri Aug 18 16:51:37 CDT 2000
> 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
More information about the thelist
mailing list