Thank you Phoebe, but most of the replies I have gotten do not take into
account the deletion of a record, and hence, the broken line of sequential
IDs.
For example (I probably should have included one in the original)
You have a database with 4 records, sequential IDs, 1 through 4:
ID MemberName
1 Name1
2 Name2
3 Name3
4 Name4
Getting the random record here is easy. Run the query and get the record
count, then do a randrange(1, recordcount), then use that to retrieve a
record. So the recordcount is 4, randrange(1,4), random number generated is,
say 3, now run a query and get record with ID 3.
ex.
<cfquery datasource="application.dsn" name="records">
SELECT * FROM tblMembers
</cfquery>
<cfset r = randrange(1,records.recordcount)>
----lets say r = 3 -----
<cfquery datasource="application.dsn" name="finalrecord">
SELECT * FROM tblMembers WHERE ID = #r#
</cfquery>
<cfoutput>
#finalrecord.MemberName#
</cfoutput>
Name3 is the winner!!!
****************************************************************************
****************
Now let's say we delete record with ID 3. If we ran the above scenario, we
would get no reocrds with an ID of 3 (3 having been deleted but the random
function coming up with the 3 in the range of 1 through recordcount.
At this point you could say IF the RecordCount after the query to get the ID
is 0, then do this all again until you get a random number that is found in
the resulting query, in this case 1,2 or 4 would work.
You could add something to this effect in a loop.
<cfif finalrecord.recordcount EQ 0> Then do this all again until we get a
hit.
This is all fine for small, not highly fragmented datasets. I wanted to come
up with a better way to do it, using less processor time, who knows, maybe
it is not significant.
Again, thanks a bunch, I was probably just not that clear in my first post.
Gary McNeel, Jr.
Project Manager - DAC-Net, Research & Graduate Studies
Rice University - Houston
[Lovett Hall] 713-348-6266 (Primary)
[DAC] 713-348-5184
[M] 713-962-0885
[H] 713-723-9240
"The genius of our ruling class is that it has kept a majority of the people
from ever questioning the inequity of a system where most people drudge
along, paying heavy taxes for which they get nothing in return."
-Gore Vidal
> -----Original Message-----
> From: Wong, Phoebe [mailto:[EMAIL PROTECTED]]
> Sent: Friday, June 16, 2000 1:10 PM
> To: [EMAIL PROTECTED]
> Cc: Corson, Berton M.; Wong, Phoebe
> Subject: selecting random record from a database
>
>
> I saw your question on CF_Talk and one of our guys (Bert Corson) came up
> with this:
>
> Note: there were 186 records in this particular database
>
> <cfquery datasource="irmsurvey" name="Contest">
> SELECT * FROM Survey WHERE ID > 186
> </cfquery>
> <cfset winner=randrange( 1, contest.recordcount)+186>
>
>
>
>
> re: Is there an easy way to pick a random record from a database. I have a
> unique ID for each record, but it is not strictly sequential because of
> record deletions.
>
> Here is my thought:
>
> 1. Run a query and get the MAX and MIN IDs.
> 2. Do a loop using them as the beginning and ending range and generate a
> random
> number between them.
> 3. See if that number exists in the ID field of the db.
> 4. If yes, get the record, if not, try again.
> 5. Do this until you get a hit.
> 6. Alternatively I guess you could gen a random number and take the next
> higher
> number unless it is the highest number.
>
> That seems way to intensive and I know there must be a cleaner way. The
> programmers must know a good way to do this.
>
------------------------------------------------------------------------------
Archives: http://www.eGroups.com/list/cf-talk
To Unsubscribe visit
http://www.houseoffusion.com/index.cfm?sidebar=lists&body=lists/cf_talk or send a
message to [EMAIL PROTECTED] with 'unsubscribe' in the body.
