SQL row from table with equal probability

user_15570240 0
Viewed 63

I have a table xyz and there are 5 rows in it. And, I have a function which returns one row from xyz table sort by randomly to the user and then user adds a suggestions to it. so, the suggestions count increased.

Table xyz is like

 id       | suggestions 
----------+--------------
 276      |        0   
 111      |        0   
 222      |        0   
 333      |        0   
 444      |        0

simple function which currently I have is like this

 SELECT * FROM xyz
 ORDER BY RANDOM()

But my aim is to get at least 3 suggestions on all of the rows, and then any row can have any number of suggestions. so, if each time one row can be selected randomly with equal probability that will do the job. so, in the first 5 instance one row of the table will be selected and once all of them will be selected, then the process repeat again with equal probability of all the rows.

What I don't want is that - one row suggestions 4, but other row suggestions less than 3.

so, if function is called 15 times, I want it to be like this

id       | suggestions 
----------+--------------
 276      |        3   
 111      |        3   
 222      |        3   
 333      |        3   
 444      |        3 
 .....

but not like this

id       | suggestions 
----------+--------------
 276      |        1   
 111      |        0   
 222      |        5   
 333      |        6   
 444      |        3 
 .....
postgresql sql
2 Answers

Just first order by suggestions then random(). This will select rows randomly where number of suggestions are less than other rows.

In below example select query is choosing rows randomly where suggestions is 2 since it's the smallest in the table.

Schema and insert statements:

 create table xyz (id int, suggestions int);
 INSERT into xyz VALUES ( 276    ,     2 );
 INSERT into xyz VALUES ( 111    ,     2 );
 INSERT into xyz VALUES ( 222    ,     3 );
 INSERT into xyz VALUES ( 333    ,     3 );
 INSERT into xyz VALUES ( 444    ,     3 );

First select query:

 SELECT *  FROM xyz
  ORDER BY SUGGESTIONS, RANDOM()

Output:

id suggestions
111 2
276 2
222 3
444 3
333 3

Second select query:

 SELECT *  FROM xyz
  ORDER BY SUGGESTIONS, RANDOM()

Output:

id suggestions
276 2
111 2
444 3
222 3
333 3

db<fiddle here

user_86516010

I had the same idea as Kazi Mohammad Ali Nur, but I would use a DESCENDING ORDER BY.

I am using Oracle and create the following script on https://livesql.oracle.com. I use floor(dbms_random.value(1, 10)) as RANDOM-Function and WHERE rownum <= 1 within the UPDATE-Statement is the old Oracle syntax for SELECT TOP to get only the first data row.

drop table xyz;
create table xyz(id number, suggestions number);
insert into xyz(id, suggestions) values (276, 0);
insert into xyz(id, suggestions) values (111, 0);
insert into xyz(id, suggestions) values (222, 0);
insert into xyz(id, suggestions) values (333, 0);
insert into xyz(id, suggestions) values (444, 0);

update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 0
111 1
222 0
333 0
444 0 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 0
111 1
222 0
333 1
444 0 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 0
111 1
222 1
333 1
444 0 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 1
111 1
222 1
333 1
444 0 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 1
111 1
222 1
333 1
444 1 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 1
111 1
222 1
333 2
444 1 
update xyz set suggestions = suggestions + 1 
where id = (SELECT x.id FROM (SELECT xyz.id FROM xyz ORDER BY xyz.suggestions, floor(dbms_random.value(1, 10)) desc) x WHERE rownum <= 1);
SELECT * FROM xyz;
ID SUGGESTIONS
276 2
111 1
222 1
333 2
444 1
user_71267530
Related