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 | +- --+-----+------+
Answers:
Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.
Method 1
Note the difference between UNION and UNION ALL
- UNION will remove duplicate rows
- UNION ALL keeps all rows
Method 2
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.
Method 3
To get 3 different rows from the table, do simply:
SELECT * FROM names WHERE Rate = 35 ORDER BY RAND() LIMIT 3;
All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0