1

I'm working on a query where users enter in points to a contest. They can enter in as many points as they have. I need to choose a winner at random but people with more points entered should technically have a better chance at getting picked.

I currently pull the query based using rand and a sum of total points per user.

The Table data looks like this: 

fname   lname   user_id points   
John    baker     1 300
Robert  backster  2 40
jason   Doe       3 900

If I were to run the query multiple times, John would have a better chance then Robert but Jason would have a better chance then both John and Robert.

select * from table order by rand()

Jason
  • 1,587
  • 1
  • 19
  • 26
  • Please show the query you're working on. We can't help you fix it if we don't know what you've written. You're not expecting us to write it for you, are you? – Barmar Oct 18 '13 at 01:40
  • select * from table ORDER BY RAND() LIMIT 1 – Jason Oct 18 '13 at 01:44
  • Could you be more specific Jack? The question was what is the query I'm working with it's just that select from the table order by rand() limit 1 That's all I have at the moment. – Jason Oct 18 '13 at 01:54
  • My bad, I thought that was a different Jason ;-) – Ja͢ck Oct 18 '13 at 01:57

1 Answers1

1

Doing it all within a query would be tough, I personally would rely a little bit on your backend. But if you have to do it all within MySQL, I would look for a weighted random selection.

From this SO post, I found this blog post that discusses it.

Basically, you are going to use some combination of the RAND() function and logarithms to achieve your result. From what I can tell, you may need to know the totals/averages to get your multiplier but that should be doable via a query as well.

Community
  • 1
  • 1
Matt Dodge
  • 10,833
  • 7
  • 38
  • 58
  • IMO it would be more helpful to just flag this question as a duplicate instead. – Ja͢ck Oct 18 '13 at 01:45
  • Probably, when I started answering I intended to write some psuedo-code for doing it outside of the db but after re-reading the question I decided to save my brain for a little bit – Matt Dodge Oct 18 '13 at 01:46
  • Great this seems to work properly. ORDER BY -LOG(1.0 - RAND()) / points – Jason Oct 18 '13 at 01:57