Increase occurrence using mysql rand()

Question

I have mysql rows as follows

id | entity | value | Frequency
1  | 2      |abc    |5
2  | 3      |123    |1
3  | 4      |456    |1
4  | 5      |789    |1
5  | 6      |xyz    |1
6  | 7      |lkh    |1
7  | 8      |jpg    |1

SELECT * FROM `table` ORDER BY RAND() LIMIT 1

I have query something like this , and this would return me the random rows. And is there any ways I could increase the occurrence of rows according to the frequency column. This means the row with Frequency = 5 should have more occurrence than those who got null values

According to the value in frequency table like fifth times more — ujwal dhakal, Nov 14 '16 at 09:30
I think what you're looking for is some kind of weighted random, which was answered in other questions ; http://stackoverflow.com/questions/1398113/sql-select-one-row-randomly-but-taking-into-account-a-weight — Sorin Lascu, Nov 14 '16 at 10:38

score 1 · Answer 1 · answered Nov 14 '16 at 09:41

1

First of all, at the moment according to your table there is no relationship between the frequency of the top row and the frequency of the other rows because of the null values. Null means missing data. If you want to indicate that the 1st row should have 5X the chance of being selected, then the rest, then use the number 1 in place of nulls.

To increase the chance of the top row to be selected, you can simply multiply the generated random number with the frequency field's value:

SELECT * FROM `table` ORDER BY RAND() * `frequency` LIMIT 1

However, if this is about ads and you need to guarantee that every 5th time the 1st ad is displayed, then you need to keep track of ads being selected in the application and select the top one if it was not selected for 4 times.

answered Nov 14 '16 at 09:41

Shadow

33,525
10
51
64

Multiplying by its own column doesn't gives the occurance as wanted i did in 10 rows and in 10th attempt i couldnt find the required rows – ujwal dhakal Nov 14 '16 at 09:49
As described, the above method gives a 5x higher **chance**. It does not guarantee, that it will appear 5x more. Think about this the following way: if you buy 5 lottery tickets, and everybody else buys just one, then you have 5x chance of winning the lottery, but it does not mean that you will ever win it. As described in the last paragraph of the answer, if you need to guarantee the occurence, then you need to track which records were shown. – Shadow Nov 14 '16 at 09:54
Yes right currently i am making for the ads so i think increasing by 5x chances wont work in this scenario i have to track down the whole tables – ujwal dhakal Nov 15 '16 at 03:34

Increase occurrence using mysql rand()

1 Answers1