Lottery Algorithm in my Case?

Question

I have 2 tables: Members and points

Members table consists of members' information and in Point member I store member's point.

Members: MemberID, FullName, Register Date, and so on.

Points: PointID, MemberIdFK, MemberPoint

I need to select 5 members randomly in a month. As you know a member with more point have more chance to win. I need a SQL query to do this for me.

Answer 1

You could try using the Rand function to get a random number and then multiply it by the points for each row, sort by value asc and  take the top 5

Something like

select

top 5 v=rand()*points from Points order by v desc

Answer 2

One way.  This requires a numbers table (see http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-numbers-table.html for a description of what a numbers table is, how to create one, and some of their many uses).  The following gives each member a chance proportional to the number of points that member has (that is if member A has twice as many points as member B, member A is twice as likely to be picked).

-- Table variable to hold intermediate result
Declare @Choices Table (MemberID int, FullName varchar(100), uID uniqueidentifier);

-- Insert one row for every point every member has and give each row a random value
Insert @Choices (MemberID, FullName, uID)
Select m.MemberID, m.FullName, NewID()
From Members m
Inner Join Points p On m.MemberID = p.MemberIdFK
Inner Join Numbers n On n.Number <= p.MemberPoint;

-- Choose the 5 members with lowest random value
;With cte As
(Select MemberID, FullName, Min(uID) As MinuID From @Choices Group By MemberID, FullName)
Select Top 5 MemberID, FullName
From cte
Order By MinuID;

Tom

Answer 3

You could try using the Rand function to get a random number and then multiply it by the points for each row, sort by value asc and  take the top 5

Something like

select

top 5 v=rand()*points from Points order by v desc

It always returns same row.

Answer 4

Here is a casino roulette simulation with reliable random number generator:

http://www.sqlusa.com/bestpractices/casinoroulette/

---

Kalman Toth Database & OLAP Architect
Paperback / Kindle: Pass SQL Exam 70-461 & Job Interview: Programming SQL Server 2012

Answer 5

Okay, the rand() function needs to be seeded to return a different random number per row. Also, I added a  a scale factor to reduce the possibility of the points value swamping the random number

declare @scale float
select @scale = max(points)*.5 from test
select @scale
select top 5 ID,points, v=rand(convert(varbinary, newid()))/@scale from test order by v desc