How to fix inconsistent mysql results?

user_16465210 0
Viewed 69

My MySQL query

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION 
(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION 
(SELECT * FROM names  WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

it should give me something like this

+----+-----+------+
| id | Rate| num  |
+----+-----+------+
| id |  75 |  987 |
| id |  75 |  987 |
| id |  35 |  987 |
+- --+-----+------+

but sometimes it only gives me 2 rows, something like this

+----+-----+------+
| id | Rate| num  |
+----+-----+------+
| id |  75 |  987 |
| id |  35 |  987 |
+- --+-----+------+
union-all sql-limit union sql-order-by mysql
3 Answers

Note the difference between UNION and UNION ALL

  • UNION will remove duplicate rows
  • UNION ALL keeps all rows
user_170161440

UNION removes duplicate rows.

The first 2 queries may return the same row and in this case one of them will only be in the results.

If you don't mind duplicate rows in the results then change to UNION ALL:

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION ALL 
(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 1) 
UNION ALL
(SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

If you want unique rows then unify the first 2 queries to only 1 with LIMIT 2 so that they return 2 rows:

(SELECT * FROM names WHERE Rate = 75 ORDER BY RAND() LIMIT 2) 
UNION ALL 
(SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 1);

I use here UNION ALL because it performs better than UNION.

user_104988280

To get 3 different rows from the table, do simply:

SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 3;
user_17668310
Related