1

I use this query:

SELECT f.id FROM articles f
JOIN ( SELECT RAND() * (SELECT MAX(id) FROM articles) AS max_id ) AS m
WHERE f.id >= m.max_id
ORDER BY f.id ASC
LIMIT 5

The problem is that it returns an unexpected number of rows. It can be 1 row or 3 rows or 5 rows. But I need exactly 5 rows.

Community
  • 1
  • 1
user8661184
  • 43
  • 1
  • 6

2 Answers2

2

A possible issue that your query has is that it only selects one record randomly and the rest of record are sequential. If you want to truly select all 5 randomly, you'll have to select them one by one:

(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random = f.id)

This works if the IDs are sequential with no gaps. If you have gaps, you'll have to change the equality to less than, and limit to one record:

(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1) UNION ALL
(SELECT f.id FROM articles f INNER JOIN (SELECT FLOOR(RAND() * (SELECT MAX(id) FROM articles) + 1) AS random) m ON m.random <= f.id LIMIT 1)

However, like dnswlt mentioned in his answer. The classical solution, which is much simpler, will work for small tables:

SELECT f.id
FROM articles f
ORDER BY RAND()
LIMIT 5
Racil Hilan
  • 24,690
  • 13
  • 50
  • 55
  • Your five joins will return 0 or 1 row, most likely 0, exept if all five calls to `RAND` yield the same value :) – dnswlt Feb 23 '18 at 21:48
  • @dnswlt Of course. It needed unions between those subqueries. I have no idea how the query ended up like that. I guess I was tired, copy/pasted the inner subquery instead of the outer one and forgot to add unions. Thanks for the note. – Racil Hilan Feb 24 '18 at 00:17
1

Your question looks like a duplicate of MySQL select 10 random rows from 600K rows fast.

Depending on how many rows you have in your table and the response time you're willing to accept, you could try the classical variant first:

SELECT f.id FROM articles f
ORDER BY RAND()
LIMIT 5

If it's less than a million, this might suffice.

Else you can apply the trick described in the linked article, and UNION ALL 5 such queries, roughly like so:

(SELECT r1.id
  FROM articles AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM articles)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1)
 UNION ALL
 (SELECT r1.id
  FROM articles AS r1 JOIN
       (SELECT CEIL(RAND() *
                     (SELECT MAX(id)
                        FROM articles)) AS id)
        AS r2
 WHERE r1.id >= r2.id
 ORDER BY r1.id ASC
 LIMIT 1)
 UNION ALL
 ... (three more queries to retrieve 5 rows)

If you have few rows, there is some likelihood of duplicates in this solution. But if you have few rows, you can use the simpler solution above anyway :)

I created an SQL Fiddle for the second variant. HTH!

dnswlt
  • 2,925
  • 19
  • 15