Search code examples
sqlselectrandomweighted

Giving Range to the SQL Column

I have SQL table in which I have column and Probability . I want to select one row from it with randomly but I want to give more chances to the more waighted probability. I can do this by 

    Order By abs(checksum(newid()))

But the difference between Probabilities are too much so it gives more chance to highest probability.Like After picking 74 times that value it pick up another value for once than again around 74 times.I want to reduce this .Like I want 3-4 times to it and than others and all. I am thinking to give Range to the Probabilies.Its Like

    Row[i] = Row[i-1]+Row[i]

How can I do this .Do I need to create function?Is there any there any other way to achieve this.I am neewby.Any help will be appriciated.Thank You

EDIT: I have solution of my problem . I have one question . if I have table as follows.

    Column1   Column2
     1         50
     2         30
     3         20

can i get?

    Column1   Column2  Column3
     1         50       50
     2         30       80
     3         20       100

Each time I want to add value with existing one.Is there any Way?

UPDATE: Finally get the solution after 3 hours,I just take square root of my probailities that way I can narrow the difference bw them .It is like I add column with

    sqrt(sqrt(sqrt(Probability)))....:-)
Solution

  • I'd handle it by something like

    ORDER BY rand()*pow(<probability-field-name>,<n>)

    for different values of n you will distort the linear probabilities into a simple polynomial. Small values of n (e.g. 0.5) will compress the probabilities to 1 and thus make less probable choices more probable, big values of n (e.g. 2) will do the opposite and further reduce probability of already inprobable values.