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. — C3roe
– C3roe, Commented Apr 7, 2021 at 13:42
Does this answer your question? How to request a random row in SQL? — DonKnacki
– DonKnacki, Commented Apr 7, 2021 at 13:44

Gordon Linoff · Accepted Answer · 2021-04-07 13:50:42Z

2

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.

answered Apr 7, 2021 at 13:50

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

PHP - Select random row from table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related