After testing my code through based on JP’s comments, I’ve realized my implementation was way too naïve and cannot be used for most datasets. For a correct weighted random implementation, see Dems’ answer on StackOverflow.
Original (flawed) implementation
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:
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:
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.
FIG1: Showing the statistical distribution of using RAND(NEWID())