> 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.
>
> Any thoughts or SQL code would be greatly appreciated.
Off the top of my head - and a horribly slow solution for larger databases.
<CFQuery name="myQuery" datasource="myDSN">
select ID
from myTable
</CFQuery>
<CFSet myList=ValueList(myQuery.ID)>
<CFSet myRND=RandRange(1,myQuery.RecordCount)>
<CFSet myValue=ListGetAt(myList,myRND)>
myValue is then the ID for the randomly selected record
I've only just got into work, and not particularly awake, so there's
probably tons of better ways to do this.
Philip Arnold
ASP Multimedia Limited
T: +44 (0)20 8680 1133
"Websites for the real world"
**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.
**********************************************************************
------------------------------------------------------------------------------
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.
