Randomly select fields where few condition met

Question

Here is the table-

+----+---------+--------+
| id | letters | status |
+----+---------+--------+
|  1 | A       |      0 |
|  2 | B       |      1 |
|  3 | C       |      0 |
|  4 | D       |      0 |
|  5 | E       |      1 |
|  6 | F       |      1 |
|  7 | G       |      0 |
|  8 | H       |      0 |
+----+---------+--------+

Its need to finds records with the conditions below-

select letters with LIMIT 3
ORDER is RAND()
status true or false both need to present but at-least a presents of letters with status TRUE but not more then 2

Desire results could be as-

+---------+--------+
| letters | status |
+---------+--------+
| B       |      1 |
| E       |      1 |
| H       |      0 |
+---------+--------+

+---------+--------+
| letters | status |
+---------+--------+
| C       |      0 |
| E       |      1 |
| H       |      0 |
+---------+--------+

But not as-

+---------+--------+
| letters | status |
+---------+--------+
| C       |      0 |
| G       |      0 |
| H       |      0 |
+---------+--------+

+---------+--------+
| letters | status |
+---------+--------+
| B       |      1 |
| E       |      1 |
| F       |      1 |
+---------+--------+

Anyone please help.

Please edit your question. Your condition 3 isn't clear. It sounds like you need to select three randomly chosen rows from among the rows with a TRUE status. Is that correct? — O. Jones, Jul 29 '14 at 11:43
What does _at least a presents of letters_ mean? That makes no sense in English. — Barmar, Jul 29 '14 at 11:46
I Edited my question.. Anyone please check.. I'm currently doing this with PHP but looking for a easy solution on mysql that will save few queries.. — Fazal Rasel, Jul 29 '14 at 12:23
@ErwinBrandstetter I think its a database related question.. Is there any solution on PostgreSQL way? — Fazal Rasel, Jul 29 '14 at 13:38
Of course there is. But what good would that be if you are using MySQL? — Erwin Brandstetter, Jul 29 '14 at 14:08
I could study on that solution and try to find solution similar solution against MySQL.. — Fazal Rasel, Jul 29 '14 at 14:18

score 1 · Answer 1 · edited May 23 '17 at 12:01

Here is a solution in Postgres as requested in the comments.

Assuming status to be NOT NULL.
Assuming at least one row with status FALSE and one with status TRUE exist at all times.

WITH cte AS (
   (
   SELECT id, letters, status
   FROM   tbl
   WHERE  status       -- 1 row with status true
   ORDER  BY random()
   LIMIT  1
   )
   UNION ALL
   (
   SELECT id, letters, status
   FROM   tbl
   WHERE  NOT status   -- 1 row with status false
   ORDER  BY random()
   LIMIT  1
   )
   )
SELECT * FROM cte
UNION ALL              -- add another random row
(
SELECT id, letters, status
FROM   tbl
LEFT   JOIN cte c USING (id)
WHERE  c.id IS NULL    -- don't select row twice
ORDER  BY random()
LIMIT  1
)
ORDER BY random();     -- order 3 rows randomly

MySQL does not supports CTEs.
All parentheses are necessary. Details:

PostgreSQL combine multiple select statements

This is not very efficient for big tables. For better performance consider this related answer:

Best way to select random rows PostgreSQL

If status TRUE and FALSE are not extremely unbalanced, I would write a plpgsql function that loops through the randomly sorted table (or selection like in the the linked answer) until I have three rows with at least one of each status. Would be much faster.

Thank you for your reply.. vote up.. I read an article tells that MySQL do not support CTEs but alternatives is to use `TEMPORARY tables` which will very inefficient for me. I tried (SELECT * FROM `test` order by rand() limit 2) union ( select * from test where status=1 order by rand() limit 1) on MySQL but it sometimes return 2 results.. Hoping someone lead me to a proper solution.. — Fazal Rasel, Jul 29 '14 at 15:28

Randomly select fields where few condition met

1 Answers1