Return number of rows based on column value
For Postgres you can use:
select guid, g.i
from the_table
cross join lateral generate_series(1, entries) as g(i);
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.
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