none
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.

    Friday, February 22, 2013 4:57 PM

Answers

  • 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


    Friday, February 22, 2013 5:43 PM
  • 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

    Monday, February 25, 2013 8:21 AM

All replies

  • 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
    Friday, February 22, 2013 5:37 PM
  • 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


    Friday, February 22, 2013 5:43 PM
  • 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.
    Friday, February 22, 2013 8:38 PM
  • 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

    Saturday, February 23, 2013 8:55 AM
    Moderator
  • 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

    Monday, February 25, 2013 8:21 AM