Results 1 to 7 of 7
  1. #1
    Join Date
    Oct 2005
    Posts
    6

    Unanswered: Lottery / Allocate Number

    I’m hoping someone can help me create a ‘lottery’ for records in a table.

    I have a table called tblPoints with a number of fields including Application_ID, Total_Points and Place_No. The following is a sample of the data

    Application_ID Total_Points Place_No
    87 105
    68 95
    49 75
    96 75
    155 75
    156 70
    144 70
    146 70

    At the moment there is no data in the Place_No field.

    I want to assign a Place_No to all records based on the number of Points (Total_Points). The highest Points value should have a place Number of 1 and so on.

    Application_ID Total_Points Place_No
    87 105 1
    68 95 2
    49 75
    96 75
    155 75
    156 70
    144 70
    146 70

    However, where a number of applications have the same points I want to randomly allocate a place number for them. Application_ID 49, 96 and 155 all have 75 points so each of the 3 applications should be randomly allocated one of the following place numbers, 3, 4 and 5. I can not allocate them based on their order in the table as it has to be seen as a ‘lottery’ and each time it is run they would expect to get a different result.

    The same thing then has to happen with the last 3 records in this sample allocating place numbers 6, 7 and 8

    I was hoping to create a stored procedure to do this, but I’ve no idea where to begin. I would appreciate any help you could give. Thank you.

    C

  2. #2
    Join Date
    May 2002
    Posts
    299
    e.g.
    select app_id, total_points, (select count(*) from tb t2 where t2.total_points>=t1.total_points) as placement
    from tb t1
    --
    -oj
    http://www.rac4sql.net

  3. #3
    Join Date
    Oct 2005
    Posts
    6
    Thank you for your reply. The result I get when I run this is

    App_id Total_Points Placement
    87 105 1
    68 95 2
    49 75 5
    96 75 5
    155 75 5
    156 70 8
    144 70 8
    154 70 8

    I’m not sure where to go from here - I’m fairly new to this. How do I now allocate the numbers 3, 4 & 5 to App_ids 49, 96 & 155 in a random order and so on?

    Thanks

    C

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Use newid() to generate a random value in a temporary recordset:
    Code:
    select	app_id,
    	total_points,
    	newid() as tie_breaker
    into	#TempTable
    from	[YourTable]
    
    select	app_id,
    	total_points,
    	count(*) as Placement
    from	#TempTable
    	inner join #TempTable ThetaTable
    		on #TempTable.total_points > ThetaTable.total_points
    		or (#TempTable.total_points = ThetaTable.total_points
    			and #TempTable.tie_breaker > ThetaTable.tie_breaker)
    group by app_id,
    	total_points
    
    drop table #TempTable
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  5. #5
    Join Date
    Oct 2005
    Posts
    6
    Thank you very much for your reply. That is brilliant.

    Just one more question, sorry. The first select statement puts all records into the Temp table, however the second select statement omits the first record (the record with the highest points value). I’ve been looking through the code and can’t spot why. Any ideas?

    Thanks again.

    C

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Change this:
    and #TempTable.tie_breaker > ThetaTable.tie_breaker
    to this:
    and #TempTable.tie_breaker >= ThetaTable.tie_breaker
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  7. #7
    Join Date
    Oct 2005
    Posts
    6
    That works perfectly. Thank you very much.

    C

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •