Good afternoon All.
I'm running into difficulties trying to get a random selection from a subset in a query.
Basically what I have is a table with a record for every New Active referral, that contains the date(active_date) the member ID(mem_id) and the referrer ID(ref_id)...
What I need to do is select all ref_ids that have 6 or more referrals(mem_ids) within a specific date range, and then randomly select one of those selected ref_ids to receive a contest bonus...
Sounds pretty simple, but I've been messing with it for hours and no luck...
This query gives me a list of the ref_ids and the count of refs each has.
SELECT ref_id, count(active_id) as refs FROM `new_actives` WHERE active_date>'2013-03-31' AND active_date<'2013-04-07' GROUP BY ref_id ORDER BY refs desc
The table has an assortment of records where the ref_id can match 1 mem_id or could potentially have an unlimited number. Currently the most any one ref_id has is 8 matching referrals (mem_ids)
Ultimately what I would like to do is to be able to randomly select a winner from the referrers that have 6 or more referrals
Do the same for those with 4 or more, and then the same for 2 or more and finally 1 or more...
I'm sure I can manually do record counts and then do the random selection, but was thinking there would be a much easier quicker way to accomplish this.
The basic random selection routine that I use for other things is not really suitable for this purpose because it takes into consideration all individual records in the table to randomly select the winner from, and I only want to consider each referrer once in each drawing, so those that have 8 referrals won't have 8 chances to win, but only 1.
This is that code:
SELECT FLOOR(RAND() * COUNT(*)) AS `offset` FROM `new_actives` WHERE active_date>'2013-03-31' AND active_date<'2013-04-07'
Any suggestions or direction would be greatly appreciated...
And if I'm going in the wrong direction, please feel free to set me straight (nicely);-))