LIMIT then RAND rather than RAND then LIMIT

Question

I'm using full text search to pull rows.
I order the rows based on score (ORDER BY SCORE) , then of the top 20 rows (LIMIT 20), I want to rand (RAND) the result set.

So for any specific search term, I want to randomly show 5 of the top 20 results.

My workaround is code based- where I put the top 20 into an array then randomly select 5.

Is there sql way to do this?

Answer 1

You can do this using an inner select. Select the top twenty rows in the inner select. In the outer select order these rows randomly and select the top five:

SELECT *
FROM (
    SELECT *
    FROM table1
    ORDER BY score DESC
    LIMIT 20
) AS T1
ORDER BY RAND()
LIMIT 5

Answer 2

For a code based solution, using arrays, try the modern Fisher-Yates Shuffle.