>-----Original Message-----
>From: Rob Wultsch [mailto:[EMAIL PROTECTED]
>Sent: Monday, May 19, 2008 11:20 AM
>To: Jerry Schwartz
>Cc: Scott Haneda; mysql@lists.mysql.com
>Subject: Re: improving random record selection
>
>On Mon, May 19, 2008 at 7:24 AM, Jerry Schwartz
><[EMAIL PROTECTED]> wrote:
>> I might not understand what this is doing, but I think it will
>preferentially sample the ids that are at the end of a gap.
>
>What don't you understand about the query or the way I described it?
[JS] I was being cautious, I didn't have the wit or time to go over it in
detail.
>
>> You say you want a "flat" distribution; by that I think you mean that
>the probability of selecting any given record is the same. If you have
>gaps in your data, I can't think of any way to do that other than be
>assigning a unique and sequential ID to each record. If you ever delete
>a record, you'd have to renumber the remaining ones. Then you'd pick off
>a random value for this unique ID.
>
>There are alternatives. (generating a random number for each row for
>example, take a look at the original conversation). Having to keep the
>sequence holeless would be a pain in the back side, but could be done
>with a trigger running something like I describe in the thread ->
>http://lists.mysql.com/mysql/212838 .
[JS] I think this would work:
SET @rand_rec_num = (SELECT CAST(FLOOR(RAND() * COUNT(*) + 1)
AS UNSIGNED) FROM bunya_map);
PREPARE get_rand_rec FROM "SELECT * FROM bunya_map LIMIT ?, 1";
EXECUTE get_rand_rec USING @rand_rec_num;
I suppose this could be put into a user function, if you only need a single
value passed back.
>
>--
>Rob Wultsch
>[EMAIL PROTECTED]
>wultsch (aim)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
