6

We are administering a sweepstakes for a client where we have a GUID tied to a contestant and the number of entries the contestant has acquired. I want to be able to draw winners based on the chance of winning corresponding to the number of entries.

GUID | Entries
--------------
1    | 1
2    | 5
3    | 2
4    | 7

So the results would be:

GUID
----
1
2
2
2
2
2
3
3
4
4
4
4
4
4
4

Basically I plan put the results in a spreadsheet and then use a random number generator to pick a row number between 1 and n, where n is the total number of entries. But of course if there is a way to easily select the winner programmatically, I'm all ears (or eyes as the case may be).

Thanks in advance.

  • 3
    What DBMS are you using? What have you tried? – mustaccio Dec 29 '16 at 15:52
  • Is there an Entries table? – McNets Dec 29 '16 at 16:04
  • Is there a specific reason why you want that to be done on the database? You normally want to offload on the DB anything that saves unnecessary data being sent to the client, but in this particular case you are doing the opposite (you want the database to send you redundant data). I plan put the results in a spreadsheet and then use a random number generator to pick a row number between 1 and n Why not return the first raw results and use a weight function on your random number (1:1 wins; 2:2-6: wins, etc.)? – jynus Dec 29 '16 at 16:18
  • Other answers provide a way to get the data you want. As an alternative to exporting to spreadsheet I reccomend you look into Order By NewID() – Joe Dec 29 '16 at 18:34
5

For interest's sake, here is a solution for SQL Server 2016 that uses the built-in [pseudo]random number generator to pick a winner for you.

First, the data:

CREATE TABLE #t
(ID int,
Entries int)

INSERT #t
VALUES
(1,1),
(2,5),
(3,2),
(4,7)

Then the code:

DECLARE @r float = RAND()

SELECT TOP 1 ID, @r
FROM (
    SELECT ID,
    Entries,
    SUM(Entries) OVER(ORDER BY ID) / CONVERT(float,SUM(Entries) OVER()) AS RunningFraction
    FROM #t
) RF
WHERE RunningFraction > @r
ORDER BY ID

You can skip the variable declaration and just use WHERE RunningFraction > RAND(), but this format makes it easier to test the functionality.

| improve this answer | |
  • I tried this option first because it specified SQL Server 2016. Sorry for not tagging the question properly. – Vin A. Jan 3 '17 at 18:31
9

For Postgres you can use:

select guid, g.i
from the_table
  cross join lateral generate_series(1, entries) as g(i);
| improve this answer | |
  • 1
    Short and to the point. (On 9.6, it even works without lateral, and without g.i). – joanolo Dec 29 '16 at 21:57
3

I don't know of any DBMS this won't work on, but there is a little more prep work for you. Specifically, you need to build a numbers table with values from 1 to at least max possible number of entries. Build this however you want - there are dozens of pieces of code out there giving examples of how to build this type of table. Make the number the table's PK. For this example, I'll call the table Numbers, and the number field num (creativity!)

INSERT INTO EndTable (guid)
SELECT guid
FROM GUID_Entries e
INNER JOIN Numbers n
   ON n.num <= e.entries
| improve this answer | |
  • 1
    Maybe you should add details on how to create the numbers table, or perhaps link to the canonical numbers table question. – Max Vernon Dec 29 '16 at 18:14
  • I hadn't seen the canonical numbers table question before - I started using them before that post was ever made - so thanks for linking that in. Mainly, I didn't give an example because there was no specific DBMS specified. While that example works fine in SQL Server, it fails in PostgreSQL, Oracle, Firebird, MySql, etc... Posting an answer that doesn't work for the questioner can leave them more confused that just saying "work it out yourself." – Laughing Vergil Dec 29 '16 at 18:30
  • Point taken! I'd venture to guess this is a question about sql-server since the OP has tagged it sql which is far too often confused with actual tag for SQL Server. – Max Vernon Dec 29 '16 at 18:42
3

Using Sql Server (2016 in my case), this gave me the answer you wanted - (not fully tested and probably many other methods).

This technique uses a Sql Server Recursive Common Table Expression.

set nocount on
Declare @GuidEntries table (Guid int, Entries int)
insert into @GuidEntries values(1, 1)
insert into @GuidEntries values(2, 5)
insert into @GuidEntries values(3, 2)
insert into @GuidEntries values(4, 7)

;WITH cteGuidEntries  AS
(
    SELECT guid, entries from @GuidEntries
    UNION ALL
    SELECT guid, entries - 1
    FROM cteGuidEntries
    WHERE entries > 1
)
SELECT guid from cteGuidEntries 
order by guid
| improve this answer | |
  • 2
    Nice recursive CTE. You may want to either describe how that part works, or provide a link to Microsoft's CTE docs. Also, you may want to clean up unnecessary comments. – Max Vernon Dec 29 '16 at 18:12
1

Similar to Laughing Vergil's answer(+1), but Oracle specific and generates the rows needed on demand based on the largest number of entries.

SELECT ge.guid
FROM GuidEntries ge
JOIN (
   SELECT level lv FROM DUAL 
   CONNECT BY Level <= (SELECT MAX(ge2.entries) FROM GuidEntries ge2)
   ) l ON l.lv <= ge.entries
ORDER BY GUID;

Setup:

--DROP TABLE GuidEntries;
CREATE TABLE GuidEntries (Guid Number(1), Entries Number(1));
INSERT ALL 
   INTO GuidEntries VALUES (1,1)
   INTO GuidEntries VALUES (2,5)
   INTO GuidEntries VALUES (3,2)
   INTO GuidEntries VALUES (4,7)
   SELECT * FROM dual;

To also get back a random row, do the following:

SELECT guid FROM
(
SELECT ge.guid
FROM GuidEntries ge
JOIN (
   SELECT level lv FROM DUAL 
   CONNECT BY Level <= (SELECT MAX(ge2.entries) FROM GuidEntries ge2)
   ) l ON l.lv <= ge.entries
ORDER BY DBMS_Random.Random
) WHERE rownum<=1;
| improve this answer | |
0

Very clumsy, but the technique might come in handy for other purposes. Using a cube as a generator and a window function to filter rows:

with t(guid,entries) as ( 
    values (1,1),(2,5),(3,2),(4,7)
) 
select guid from (
    select guid, entries, row_number() over (
                                  partition by guid
                                  order by guid asc nulls last
                                         , entries asc nulls last) as rn
    from t 
    group by cube(guid, guid, entries, entries)
) as x
where guid is not null 
  and rn <= entries

GUID       
-----------
      1
      2
      2
      2
      2
      2
      3
      3
      4
      4
      4
      4
      4
      4
      4

I would not bet my life on it, but ordering by rand() and fetching the first row might reflect the probability determined by entries

....
where guid is not null 
  and rn <= entries+1
order by rand()
fetch first 1 rows only

Note that we need an ORDER BY in the WINDOW clause, otherwise a null might occupy a low row_number. It appears as if both Postgresql and DB2 by default orders nulls last, this might not be the case for other vendors so for safety reasons it's probably best to explicitly declare this.

| improve this answer | |
  • I think you're of by 1 (if I reproduce this on PostgreSQL, I get 2 ones, 6 twos, 3 threes, – joanolo Dec 29 '16 at 21:54
  • @joanolo, thanks for poibnting this out. The query where non-deterministic which caused different behavior. I updated the query and tested with PostgreSQL 9.5.5 and DB2 10.5.6 – Lennart Dec 30 '16 at 7:38
-1

Not exact as it will miss missing entries but it probably does what you want

set nocount on
Declare @GuidEntries table (Guid int, Entries int)
insert into @GuidEntries values(1, 1)
insert into @GuidEntries values(2, 5)
insert into @GuidEntries values(3, 2)
insert into @GuidEntries values(4, 7)
insert into @GuidEntries values(5, 1)
insert into @GuidEntries values(6, 2)

select g.Guid 
from @GuidEntries g
join (select distinct entries from @GuidEntries) ge
      on g.Entries >= ge.Entries 
order by g.Guid

Could even get a random

with cte as 
(
select g.Guid, ROW_NUMBER() over(order by g.GUID) as rn
from @GuidEntries g
join (select distinct entries from @GuidEntries) ge
      on g.Entries >= ge.Entries 
)
--select select C1.Guid FROM CTE C1
select C1.Guid, C1.rn 
FROM CTE C1 
where C1.rn = (select cast(rand()*count(*) as int) + 1  from cte)

If you have small max on entries
But I like the answer from Scott better

select GUID from talble where Entries = 1 
union all 
select GUID from talble where Entries = 2 
union all
select GUID from talble where Entries = 3 
union all
select GUID from talble where Entries = 4 
union all
select GUID from talble where Entries = 5 
union all
select GUID from talble where Entries = 6 
union all
select GUID from talble where Entries = 7 
union all
select GUID from talble where Entries = 8 
union all
select GUID from talble where Entries = 9 
| improve this answer | |
  • Oops - You mean for all of those WHERE clauses where Entries >=, right? – Laughing Vergil Dec 29 '16 at 18:32
  • Really dv a working answer? – paparazzo Dec 29 '16 at 18:39
  • @LaughingVergil No - it works for me – paparazzo Dec 29 '16 at 18:49

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.