Weighted random selections in SQL Server
There are no built-in functions for selecting weighted averages in SQL Server. Fortunately it's a simple task to do so oneself.
We'll use this table as an example:
CREATE TABLE #tmp
(
Name varchar(64),
Points int
)
INSERT INTO #tmp VALUES ('Mark', 25);
INSERT INTO #tmp VALUES ('Jakob', 12);
INSERT INTO #tmp VALUES ('Peter', 17);
INSERT INTO #tmp VALUES ('Anders', 0);
INSERT INTO #tmp VALUES ('Kirsten', 33);
INSERT INTO #tmp VALUES ('Mads', 4);
This table represents a list of players in an arbitrary game. The more points you have, the bigger the chance of winning. It has to be weighted, meaning that the person with just 4 points may win, but is unlikely to do so.
The RAND() function in SQL Server returns a floating point number between 0 and 1. Multiplying that with our points gives a random weight based on the amount of points. Unfortunately the RAND() function is seeded once for each query, not for each row - meaning that for each row RAND() will yield the same result, effectively multiplying the points with a constant all the way through. We need to provide a new seed for the RAND() function for each row. NEWID() returns a new unique identifier that may be used as a seed if cast to VARBINARY:
SELECT Name, Points, RAND(CAST(NEWID() AS VARBINARY)) * Points AS Weight FROM #tmp ORDER BY Weight DESC
Name Points Weight
Peter 17 15,9795741766356
Mark 25 14,9122204505153
Kirsten 33 9,67888480542761
Jakob 12 9,38697608441358
Mads 4 0,833340539027792
Anders 0 0
And here we have the result ordered by weight. As you can see, although Kirsten has the most points, Peter ended up winning the competition.