Search code examples
mysqlrandomsql-order-bysql-limit

retrieve random id_post sql

I have this table:

+---------+------+
| id_post | post |
+---------+------+
| 24      | A    |
| 26      | B    |
| 39      | C    |
| 57      | D    |
+---------+------+

I want to retrieve the a random id_post

SELECT id_post
FROM  posts
WHERE rand()

I would like to retrieve a single value 24, 26, 39 or 57.

How can I make that work?

Solution

  • If you have only four rows, it doesn't much matter how you do it. However, if you have a large table, you would like to limit the amount of data being sorted.

    One method is:

    select p.id_post
from posts p cross join
     (select count(*) as cnt from posts) pp
where rand() < 100.0 / pp.cnt
order by rand()
limit 1;

    Although this requires scanning the table, it does not require sorting the entire table.