How do you randomly select a table row in T-SQL based on an applied weight for all candidate rows?
For example, I have a set of rows in a table weighted at 50, 25, and 25 (which adds up to 100 but does not need to), and I want to select one of them randomly with a statistical outcome equivalent to the respective weight.
The "incrementally carrying a an accumlating[sic] weight sum" part is expensive if you have a lot of records. If you also already have a wide range of scores/weights (ie: the range is wide enough that most records weights are unique. 1-5 stars probably wouldn't cut it), you can do something like this to pick a weight value. I'm using VB.Net here to demonstrate, but this could easily be done in pure Sql as well:
Function PickScore()
'Assume we have a database wrapper class instance called SQL and seeded a PRNG already
'Get count of scores in database
Dim ScoreCount As Double = SQL.ExecuteScalar("SELECT COUNT(score) FROM [MyTable]")
' You could also approximate this with just the number of records in the table, which might be faster.
'Random number between 0 and 1 with ScoreCount possible values
Dim rand As Double = Random.GetNext(ScoreCount) / ScoreCount
'Use the equation y = 1 - x^3 to skew results in favor of higher scores
' For x between 0 and 1, y is also between 0 and 1 with a strong bias towards 1
rand = 1 - (rand * rand * rand)
'Now we need to map the (0,1] vector to [1,Maxscore].
'Just find MaxScore and mutliply by rand
Dim MaxScore As UInteger = SQL.ExecuteScalar("SELECT MAX(Score) FROM Songs")
Return MaxScore * rand
End Function
Run this, and pick the record with the largest score less than the returned weight. If more than one record share that score, pick it at random. The advantages here are that you don't have to maintain any sums, and you can tweak the probability equation used to suit your tastes. But again, it works best with a larger distribution of scores.