MySQL: How to retrieve a random row or multiple random rows?

Question

I have a MySQL database table that stores the URLs of photos. I need to pull 5 random records from the database of a particular type. I can pull 5 records like this:

SELECT Photos.* 
FROM Photos 
WHERE Photos.Type_ID = 4 
LIMIT 5

Now I need help trying to figure out how to pull different records every time. How can I retrieve random rows from this result set?

Dup of [What is the best way to request a random row in pure SQL?](http://stackoverflow.com/q/19412/90527) — outis, Mar 29 '12 at 18:09

score 8 · Accepted Answer · answered Jan 03 '11 at 17:49

8

You can use ORDER BY RAND() to get random rows in your query.

answered Jan 03 '11 at 17:49

girasquid

15,121
2
48
58

8

You've just called the wrath of SQL's upon you. – Halil Özgür Apr 01 '11 at 08:48
11

It's actually can be very slow on large data-sets. – Andrey Chernih Sep 12 '11 at 07:56

Alex · Answer 2 · 2011-01-03T17:51:46.110

6

SELECT Photos.* 
FROM Photos 
ORDER BY RAND()
LIMIT 5

edited Jan 03 '11 at 17:51

answered Jan 03 '11 at 17:50

Alex

403
3
5

score 2 · Answer 3 · answered Jan 03 '11 at 17:54

2

Google points to this detailed page. Looks like it works. I am sure it can't ensure distinct record each time, but worth trying. http://akinas.com/pages/en/blog/mysql_random_row/

answered Jan 03 '11 at 17:54

Nishant

54,584
13
112
127

MySQL: How to retrieve a random row or multiple random rows?

3 Answers3

Linked

Related