random: MySQL SELECT *and* LIMIT RAND() range possible?

vendredi 16 avril 2021

I'm trying to pull a random number of records from MySQL between a range, say 100 and 500.

This works:

SELECT  *,
        FLOOR(RAND() * (500-100) + 100) AS 'RandomValue'
FROM    sample
ORDER   BY RAND()

Except that pulls an enormous dataset.

What I'd really like to do is this:

SELECT  *,
        FLOOR(RAND() * (500-100) + 100) AS 'RandomValue'
FROM    sample
ORDER   BY RAND()
LIMIT   'RandomValue'

so I'm getting a different number of records every time.

(I'm ensure uniqueness elsewhere.)

How the fsck is this done?

random