Re: sql question

> i'm trying to select a random row from a select statement,
> what is the best possible way to do this?

hi kenny

several methods come to mind but a little more context would help

1. does your table have an identifier that contains numbers?  use a random
number function with lowest/highest id values as the range (these may have
to be obtained beforehand with a separate SELECT MIN(id), MAX(id) query),
then query for the id number that is equal to the smallest of all those ids
that are equal to or higher than the random number -- this way the random
number does not have to "hit" a real id number, and will work even if there
are gaps in the id numbers

   select * from yourTable
      where ID =
          (select min(ID) from yourTable
              where ID >= #RandRange(minID,maxID)#)

where #RandRange(minID,maxID)# is cold fusion syntax

2. does your database support the TOP or LIMIT keyword?  then choose
something absurd to sort on (e.g. 2nd through 5th digits of phone number),
and pick the first row back

if the table doesn't change a lot, however, this method typically always
returns the same row, so your randomness is based on how often you change
the sort key

3. some problems are best solved by avoiding them altogether -- if you need
to show a random row, maybe for illustrative purposes, perhaps you can show
a sample row instead?

   select * from yourTable
     where sample='Y'

this works less well for a script that shows, say, a new random tip on the
web page every day, because then you have to go in daily and change which
row the sample flag is turned on


rudy.ca

HWG hwg-techniques mailing list archives, maintained by Webmasters @ IWA