Search code examples
sqlrandomrepeat

Is there a way to get a fixed number of random rows from an SQL table while allowing for repetions

For the example here we will use the table NAMES and I need 3 rows returned.

id name
1 Alpha
2 Beta
3 Chi
4 Delta
5 Epsilon

I know the code SELECT * FROM names ORDER BY RAND() LIMIT 3; will return 3 randomized rows. But it is exclusively three different rows.

Is there a way to in a query get three rows that do not influence each other, and thus allow for repetion? So a result could be Beta, Alpha, Delta. Or Beta, Epsilon, Beta, or even Beta, Beta, Beta.

Solution

  • Try achieving this via independent subqueries that are stitched together using union all

    SELECT * FROM (SELECT id, name FROM names ORDER BY RAND() LIMIT 1) d
UNION ALL
SELECT * FROM (SELECT id, name FROM names ORDER BY RAND() LIMIT 1) d
UNION ALL
SELECT * FROM (SELECT id, name FROM names ORDER BY RAND() LIMIT 1) d

    Note: I recommend you don't rely solely on "select *", specify the columns you need.

    Try running it here (using Postgres) more than once: https://dbfiddle.uk/IwNV0z34

    Another method would be to multiply the number of rows by the wanted sample size (here it is 3) using a cross join then selecting from that:

    select
    id, name, cj.n
from names
cross join (select 1 n union all select 2 union all select 3) cj
order by rand()
limit 3

    see both methods working here: https://dbfiddle.uk/bhBLmOSv

    nb: random() is used in the fiddles as the alternative to rand()