Re: select random row from a group

Thomas，

The following is an approach to select randomly one record from the
whole lot.  Perhaps you can get it to do what you want.  But I would
recommend, either splitting the different in different tables (at least
temp tables for this queries).

Steps:
1) Add and AUTO_INCREMENT column to the table (eg. recordID type=int)
2) Try this select statement:
    SELECT *
    FROM randtest
    WHERE recordID = ( FLOOR( RAND() * MAX( recordID ) + 1 ) )

Points to note:
    -The RAND result is multiplied by the max recordID (you might
find counting the records easier, as this will error, if the index has
missing records)
    - I have added the +1, so that the result of the Floor never
returns 0, you can use CEIL if you wish it is the same difference, just
remove the +1

Hope this helps.

Corey Scott
/* ================================================================ *\
|    "My life is spent in one long effort to escape from the
|
|    common places of existence.  These little problems
help            |
}    me to do so."               -Sherlock
Holmes                    |
\* ================================================================ */


---
[This E-mail scanned for viruses by Declude antiVirus]