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

Question

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.

`union all` 3 individual queries, each limited to 1 row. – Paul Maxwell Apr 04 '23 at 00:49 — Paul Maxwell, Apr 04 '23 at 00:49

Paul Maxwell · Answer 1 · 2023-04-04T01:06:29.757

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()

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

1 Answers1