Get 5 random rows from MySQL DB

Question

I have searched all over for an answer and although people say not to use the ORDER BY RAND() clause, I think for my purposes it is ok as this is for a competition which barely has more than a few hundred records at a time PER competition.

So basically i need to retrieve 5 random records from a competition entries table. However any loyalty customers will received an additional EXTRA entry so example:

compEntryid | firstName | lastName | compID |
1           |  bob      |  smith   | 100
2           |  bob      | smith    | 100
3           |  jane     | doe      | 100
4           |  sam      | citizen  | 100

etc

So we are giving the loyalty members a better chance at winning a prize. However im a little worried that the returned result from a usual ORDER BY RAND() can include 2 entries of the SAME person ? What is an optimised method to ensure that we truly have 5 random records but at the same time giving those extra entrants a better or (weighted) chance ? Happy to use multiple queries, sub-queries or even a mix of MySQL and PHP ? Any advice is deeply appreciated thank you !

Bass

EDIT:

These 2 queries both work!

query1

  SELECT concat(firstName, " ", lastName) name,id, email 
    FROM t WHERE 
      RAND()<(SELECT ((5/COUNT(id))*10) FROM t) 
      group by email ORDER BY RAND()  limit 5;

query2

 select distinct 
    email, id, firstName, lastName from 
    (
    select id ,
    email, firstName , lastName , compID, rand()/(select count(*) from t where 
                                             email=t1.email 
                                             ) as rank
    from t t1 
    where compID = 100 
    order by rank) t2 limit 5;

http://sqlfiddle.com/#!2/73470c/2

Answer 1

If you have a few hundred record, I think that order by rand() solution should be fine: subquery will order weighting number of entries, but duplicates remains. Parent SELECT will take the first 5 distinct rows.

SELECT DISTINCT firstName , 
                lastName , 
                compID 
FROM
( SELECT compEntryid ,firstName , lastName , compID, rand()/(select count(*) 
  FROM   t 
  WHERE  firstName=t1.firstName AND
         lastName = t1.lastName) AS rank
  FROM   t t1 
  WHERE  compID = 100 
  ORDER BY rank) t2 
LIMIT 5

Fiddle

Answer 2

I think you will need to use a sub query if you want to return a compEntryid.

SELECT t.firstName, t.lastName, t.compID, MIN(compEntryid)
FROM t
INNER JOIN
(
    SELECT DISTINCT firstName, lastName, compID
    FROM t
    ORDER by rand() 
    LIMIT 5
) t2
ON t.firstName = t2.firstName
AND t.lastName = t2.lastName
AND t.compID = t2.compID
GROUP BY t.firstName, t.lastName, t.compID;

This uses a sub query to get 5 random firstName / lastName / compID. Then joins against the table to get the MIN compEntryId.

However not certain about this. Think it will eliminate the duplicates in the sub query before performing the order / limit, which would prevent someone with more entries having more chances.

EDIT

More of a play and I think I have found a solution. Although efficiency is not one of its strong points.

SELECT MIN(compEntryid), firstName, lastName, compID
FROM
(
    SELECT firstName, lastName, compID, compEntryid, @seq:=@seq+1 AS seq
    FROM
    (
        SELECT firstName, lastName, compID, compEntryid
        FROM t
        ORDER by rand()
    ) sub0
    CROSS JOIN (SELECT @seq:=0) sub1
) sub2
GROUP BY sub2.firstName, sub2.lastName, sub2.compID
ORDER BY MIN(seq)
LIMIT 5

This has an inner sub query that gets all the records in a random order. Around that another sub query adds a sequence number to the records. The outer query groups by the name, etc, and orders by the min sequence number for that name. The compEntryId is just grabbed as the MIN for the name / competition (I am assuming you don't care too much about this).

This way if someone had 5 entries the inner sub query would mix them up in the list. the next sub query would add a sequence number. At this stage those 5 entries could be sequence numbers 1 to 5. The outer one would order by the lowest sequence number for the name and ignore the others, so of those 5 only sequence number 1 would be used and 2 to 5 ignored, with the next selected person being the one with sequence number 6.

This way the more entries they have the more likely they are to be a winner, but can't be 2 of the 5 winners.

With thanks to kiks73 for setting up some sqlfiddle data:-

http://sqlfiddle.com/#!2/cd777/1

EDIT

A solution based on that above by @kiks73. Tweaked to use a non correlated sub query for the counts, and eliminates a few uncertainties. For example with his solution I am not quite sure whether MySQL will chose to do the DISTINCT by implicitly doing a GROUP BY, which would also implicitly do an orderering of the results prior to doing the limit (it doesn't seem to, but I am not sure this behaviour is defined).

SELECT t.firstName , 
        t.lastName , 
        t.compID,
        MIN(rand() / t1.entry_count) AS rank
FROM
(
    SELECT firstName, lastName, compID, COUNT(*) AS entry_count
    FROM   t 
    GROUP BY firstName, lastName, compID
) t1
INNER JOIN t
ON  t.firstName=t1.firstName 
AND t.lastName = t1.lastName
AND t.compID = t1.compID
GROUP BY t.firstName, t.lastName, t.compID
ORDER BY rank
LIMIT 5