Answered by:
Lottery Algorithm in my Case?

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.
Question
Answers

One way. This requires a numbers table (see http://sqlserver2000.databases.aspfaq.com/whyshouldiconsiderusinganauxiliarynumberstable.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
 Marked as answer by Iric WenModerator Monday, March 04, 2013 5:55 AM

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
 Marked as answer by Iric WenModerator Monday, March 04, 2013 5:55 AM
All replies


One way. This requires a numbers table (see http://sqlserver2000.databases.aspfaq.com/whyshouldiconsiderusinganauxiliarynumberstable.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
 Marked as answer by Iric WenModerator Monday, March 04, 2013 5:55 AM


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 70461 & Job Interview: Programming SQL Server 2012 
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
 Marked as answer by Iric WenModerator Monday, March 04, 2013 5:55 AM