Get random row from MySQL table

Question

I'm trying to fetch a random row from a table in MySQL. I'm using the following query:

SELECT *
FROM my_table
WHERE id IN (
    SELECT ROUND((RAND() * (MAX(id)-MIN(id)))+MIN(id)) AS id
    FROM my_table
);

I wish to understand why that query sometimes returns more than 1 row when the inner query always get 1 row?

Thanks

This is bad anyway, since it would only work if all your ids were consecutive, which in reality they would not be as soon as records get deleted. — CBroe, Mar 01 '15 at 11:18
possible duplicate of [quick selection of a random row from a large table in mysql](http://stackoverflow.com/questions/211329/quick-selection-of-a-random-row-from-a-large-table-in-mysql) — Ja͢ck, Mar 01 '15 at 11:24
@CBroe Please could you explain why it is bad? Actually all ids are consecutive, but not working as expected. — atoo, Mar 01 '15 at 13:04

score 1 · Answer 1 · answered Mar 01 '15 at 11:20

1

Just to give you a hint, this will always return 1 random row so refactor your query.

Note: Your query will be very expensive if you have many rows in DB.

SELECT * FROM table
WHERE column = 'data'
ORDER BY RAND()
LIMIT 1

answered Mar 01 '15 at 11:20

BentCoder

12,257
22
93
165

I got ROUND((RAND() * (max-min))+min) from http://stackoverflow.com/questions/6550155/mysql-get-a-random-value-between-two-values?lq=1 What makes my query very expensive? – atoo Mar 01 '15 at 13:08

score 0 · Answer 2 · edited Mar 01 '15 at 11:49

0

use this instead

SELECT *
FROM my_table
WHERE id = (
    SELECT ROUND((RAND() * (MAX(id)-MIN(id)))+MIN(id)) AS id
    FROM my_table
);

edited Mar 01 '15 at 11:49

Strawberry

33,750
13
40
57

answered Mar 01 '15 at 11:21

LEADER

113
8

I first did like that, before turning WHERE id = to WHERE id IN, but same unexpected behavior – atoo Mar 01 '15 at 13:11
I think u have duplicated ids in ur table, a solution is to add distinct close: SELECT * FROM my_table WHERE id = ( SELECT Distinct ROUND((RAND() * (MAX(id)-MIN(id)))+MIN(id)) AS id FROM my_table ); – LEADER Mar 01 '15 at 13:19
id is the PK in the table – atoo Mar 01 '15 at 13:26

score 0 · Answer 3 · answered Mar 01 '15 at 23:13

I ended with the following stored procedure

BEGIN
    SET @indx=0;

    DROP TEMPORARY TABLE IF EXISTS temp;
    CREATE TEMPORARY TABLE temp
        SELECT (@indx := @indx+1) AS indx, mt.id
        FROM my_table mt;

    BEGIN
        DECLARE v_random_index INTEGER;
        DECLARE v_random_id INTEGER;

        SELECT ROUND((RAND() * (MAX(indx)-MIN(indx)))+MIN(indx)) INTO v_random_index
        FROM temp;

        SELECT id INTO v_random_id
        FROM temp
        WHERE indx=v_random_index;

        SELECT *
        FROM my_table mt
        WHERE id=v_random_id;
    END;
END;

Get random row from MySQL table

3 Answers3