Return number of rows based on column value

Question

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.

Answer 1

For Postgres you can use:

select guid, g.i
from the_table
  cross join lateral generate_series(1, entries) as g(i);

Answer 2

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.

Answer 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

Answer 4

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

Answer 5

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;

Answer 6

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.

Answer 7

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