PHP - Select random row from table

Question

My table has around 10k rows. How can I query select 1 random row of the last 10 added rows? I have a date column that is datetime of when the row was added.

This is one I use to get the last 10 rows:

SELECT id FROM mytable order by date desc LIMIT 10

I know I can do SELECT id FROM mytable ORDER BY RAND() LIMIT 1 but that just choose any random row, not from the last 10 rows.

Write the query that gets the last 10 added records, and then use that as a sub-query in a query that randomly orders the results and applies a limit of 1. — CBroe, Apr 07 '21 at 13:42
Does this answer your question? [How to request a random row in SQL?](https://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql) — DonKnacki, Apr 07 '21 at 13:44
@Cindy . . . Tag your question with the database you are using. — Gordon Linoff, Apr 07 '21 at 13:50
@Cindy . . . PhpMyAdmin is a GUI interface. Presumably the database is MySQL. — Gordon Linoff, Apr 07 '21 at 15:19

score 2 · Accepted Answer · answered Apr 07 '21 at 13:50

One method uses a subquery:

SELECT t.*
FROM (SELECT id
      FROM mytable 
      ORDER BY date DESC
      LIMIT 10
     ) t
ORDER BY rand()
LIMIT 1;

This version uses the syntax conventions for MySQL.

You can use select * in the subquery to fetch the whole row.

PHP - Select random row from table

1 Answers1