generating 10 random id numbers form a table in a string

Question

i have an id table like this from 1 to 30

|  id  |
|  1   |
|  2   |
|  3   |
|  4   |
|  5   |
|  .   |
|  .   |
|  .   |
|  30  |

what i want to do is get 10 random ids from it in a string like this

(1,2,3,4,5,6,7,8,9,10)

what i figured out was

https://www.db-fiddle.com/f/3gTHLSTdFEac7dKbDcU43d/3

https://www.db-fiddle.com/f/3gTHLSTdFEac7dKbDcU43d/4

SELECT
    CONCAT_WS(", ",
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids)),
        CEIL(RAND() * (SELECT MAX(id) FROM ids))
    ) AS random_ids

is there a faster and more efficient way without using ORDER BY RAND()?

and is there also a way to make them unique like having a gap between them?

@SalmanA yes and if there is a way for uniquness that would be good — Joe Doe, Jul 17 '19 at 12:47
If all you want is 10 random numbers between 1 and 30, what's the point of the table? — Strawberry, Jul 17 '19 at 12:57
Still, I think I'd just find the max value and generate 10 random numbers in application code based off of that — Strawberry, Jul 17 '19 at 13:00
@Strawberry i thought of that then thought that sql could possibly make an application like that in itself — Joe Doe, Jul 17 '19 at 13:03

Chris Wheeler · Accepted Answer · 2019-07-18T14:45:14.803

2

SELECT `id` FROM `table` ORDER BY RAND() LIMIT 10

Updated fiddle: https://www.db-fiddle.com/f/3gTHLSTdFEac7dKbDcU43d/1

Edit: The question was updated to exclude ORDER BY RAND().

You could do multiple queries. One to get the number of records in the database, and then select random values one at a time using OFFSET. I believe this would prevent a full table scan and be faster than a simple ORDER BY RAND() on a large data set.

Updated fiddle selecting one random id using this technique: https://www.db-fiddle.com/f/3gTHLSTdFEac7dKbDcU43d/5

SET @r := (SELECT FLOOR(RAND() * (SELECT COUNT(*) FROM `table`)));
SET @sql := CONCAT('SELECT `id` FROM `table` LIMIT 1 OFFSET ', @r);
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;

Credit: Selecting random rows with MySQL

edited Jul 18 '19 at 14:45

answered Jul 17 '19 at 12:46

Chris Wheeler

1,623
1
11
18

i thought about using ORDER BY RAND() but it uses a full scan and makes the query slow – Joe Doe Jul 17 '19 at 12:49
This is HORRIBLY inefficient (for large tables) since it will first generate a random number for EVERY record in the table, then sort ALL records by that number and then only return the top 10. Imagine doing this on a multi-million records table... For a table with 30 records... meh. – RobIII Jul 17 '19 at 12:49
1

Sure, but the question was quite specific that they had 30 ids. It runs in 80ms on db-fiddle. – Chris Wheeler Jul 17 '19 at 12:51

score 1 · Answer 2 · answered Jul 17 '19 at 12:49

1

You could use ORDER BY RAND() followed by GROUP_CONCAT:

SELECT GROUP_CONCAT(id SEPARATOR ', ') AS random_ids
FROM (
    SELECT id
    FROM t
    ORDER BY RAND()
    LIMIT 10
) AS x

answered Jul 17 '19 at 12:49

Salman A

262,204
82
430
521

i would like not use `order by rand()` because it does a full scan maybe a shorter way to rewrite my query – Joe Doe Jul 17 '19 at 12:50
I am afraid there isn't any other solution. If it is random then every row has same chance of getting selected. – Salman A Jul 17 '19 at 12:51

generating 10 random id numbers form a table in a string

2 Answers2