Re: weighted random selection of record

Originally posted by Stuart McGraw
> I want a query that will randomly select a row from
> a table but with a probability determined by a "weight"
> value stored in the rows. For example, if I have the
> table
>
> id weight
> -- ------
> 1 .6
> 2 .1
> 3 .3
>
> What I tried was
>
> SELECT TOP 1 * FROM tbl ORDER BY rnd()*[weight]
>
> where rnd() is a function that returns a uniformly
> distributed real number between 0 and 1. This almost
> does the right thing. The problem is that the resulting
> distribution is not what one would (naively) hope.
>
You need to make use of the weighting in the WHERE clause to affect the chances of selecting a record, more like this:

SELECT TOP 1 * FROM
( SELECT * FROM tbl WHERE rnd() <= [weight] ORDER BY rnd() )

(I don't use this DBMS, so syntax may be dodgy).

This assumes that [weight] is a number between 0 and 1.

The logic is:

1. Select each record with a probability proportional to its weight:

2) Randomly sort the records returned:

ORDER BY rnd()

3) Select just 1st record from the randomly selected and sorted set:

SELECT TOP 1 * FROM (...)

--
Posted via http://dbforums.com