sql server - Random Weighted Choice in T-SQL -
how randomly select table row in t-sql based on applied weight candidate rows? for example, have set of rows in table weighted @ 50, 25, , 25 (which adds 100 not need to), , want select 1 of them randomly statistical outcome equivalent respective weight. dane's answer includes self joins in way introduces square law. (n*n/2) rows after join there n rows in table. what more ideal able parse table once. declare @id int, @weight_sum int, @weight_point int declare @table table (id int, weight int) insert @table(id, weight) values(1, 50) insert @table(id, weight) values(2, 25) insert @table(id, weight) values(3, 25) select @weight_sum = sum(weight) @table select @weight_point = floor(((@weight_sum - 1) * rand() + 1), 0) select @id = case when @weight_point < 0 @id else [table].id end, @weight_point = @weight_point - [table].weight @table [table] order [table].weight desc this go through table, setting @id each record's id value while @ ...