2

I am developing a Lucky draw concept as following

There will be multiple options at database we have to get the random options according to percentage of appearance

Database table is as following 

option_id |   option_name |  option_type | option_in_day(%) |  option_status
----------------------------------------------------------------------
1             $2 Off         2             100                 1
2             $3 Off         2             95                  1
3             $4 Off         2             95                  1
4             $5 Off         2             90                  1
5             $8 Off         2             90                  1
6             $60 Cashback   2             10                  1
7             $100 Cashback  2             5                   1
8             Umbrela        2             50                  1
9             Iphone         2             2                   1
10            International
              Tour           2             1                   1
11            Fidget 
              Spinner        2             70                  1
12            Free 
              membership     2             30                  1
13            Samsung S8     2             10                  1
14            $20 Off        2             60                  1
15            Travel Card    2             50                  1
16            Soft Toys      2             70                  1

Now from this table I want to get random 8 option according to percentage.

Less percentage option chance to retrieve in result will be less.

I have tried with random function in sql but can't reach the requirement.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175
Kashyap Patel
  • 1,139
  • 1
  • 13
  • 29
  • check this [link](https://stackoverflow.com/questions/580639/how-to-randomly-select-rows-in-sql). Did you try this? `SELECT * FROM table_name ORDER BY RAND() LIMIT 8` – Regolith Jun 28 '17 at 09:02
  • i have already tried @Regolith but i have to do it based on percentage given in data – Kashyap Patel Jun 28 '17 at 09:07

1 Answers1

2

This is how you should be able to do it:

set @percentage = 100 * rand();
select *
from table_name
order by table_name.percentage >= @percentage, rand()
limit 0, 8;

This query stores a random percentage into a variable. Then, we order the query by two criterias. The first criteria is that the percentage of the table is higher or equal to the percentage randomized. This makes sure that if there are elements both above and below the randomized percentage, then the elements above will be preferred. Elements having similar result in the first ordering criteria will be randomly ordered.

Lajos Arpad
  • 64,414
  • 37
  • 100
  • 175