Random row with extras

[stvn]

Hello everybody,

I need a little help with extracting a random row from a DB table.

I have a forum and I want to make weekly draws for people who posted that specific week so they can win stuff.

My current code is simple:

SELECT * FROM forum_members WHERE number_of_posts>0 ORDER by RAND() LIMIT 1

 

but I want to change that code somehow because this way users have equal chance regardless of how many posts they have, it would be more fair if users with more posts to have more chance of winning.

 

Any ideas?

 

Thank you ans sorry for my english, I hope you understud what I ment.

 

[fenway]

I'm pretty sure DISTINCT happens before ORDER BY..... so why not

 

SELECT DISTINCT user_id FROM forum_members WHERE number_of_posts>0 ORDER by RAND() LIMIT 1

[stvn]

Thank you for your reply fenway, but unfortunately this is not what I'm lookong for, I guess I just can't explain,let me try again:

 

So, my table looks like this:

 

idnameposts

1user19

2user25

3user33

4user41

5user50

 

Now if I'd make the draw using a hat and paper clippings I'd write user1 on 9 pieces of paper, user2 on 5 pieces, user3 on 5 pieces, etc.. so user1 has the biggest chance of winning.

 

The question is that can this be done in some simple way and can you or anyone give me some info on where and how should i start, and as you probably noticed I'm no guru at this , just trying to lear.

Thank you.

[fenway]

Sorry, I misread your original post, I thought you wanted them all weighted equally.

 

The problem is that SQL doesn't really have a mechanism for "extrapolating" rows in a way that would make this easy.

 

Depending on how "random" you want it to be, you can multiply the number of posts by RAND(), and pick the highest value...

[stvn]

Thank you, I'll try it this way.