Best unofficial Apache Server developers community

Answers

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

⇧

⇩

82 views

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?

mysql

asked January 3, 2011 11:47 am CST

Andrew

posted via StackOverflow

3 Answers

Newest Most Voted

⇧

⇩

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

answered January 3, 2011 12:23 pm CST

Luke

⇧

⇩

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

answered January 3, 2011 12:23 pm CST

Alex

⇧

⇩

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 January 3, 2011 12:23 pm CST

Nishant

Your answer

Similar discussion threads

Could not retrieve catalog from remote server - random
November 10, 2010 10:40:47 AM
I have one of 18+ servers in an environment that just started having a problem when attempting to do an update. On the node I enter the command 'puppetd -t --debug --trace'. I don't see anything obvious but the error is: "Could not retrieve…

RE; random() does not keep value in subquery
December 31, 2010 06:40:41 AM
Hi All, Hmm.... Probably need to join the email list. Did a bit more research into this - if you place random() in a seperate view yo can control the behaviour a little better.. Given CREATE VIEW randomise as select abs(random()) as r; With…

Random Document
September 21, 2010 12:28:53 PM
Hi, Is there a good way to get a random document from a database. I'm currently using a view that does: function(doc) { emit(Math.random(), doc); }; But as this isn't deterministic, I'm pretty sure it's wrong. I've done a bit of googling,…

Some random questions about 3.0
October 28, 2010 01:05:25 PM
Hi. I am using 3.0.2 and had just a few questions. 1. When I create an sql session by providing my own connection, do I still use sess.commit() or is it up to me to do commits/rollbacks using connection object? Also, why is connection being…

random think time
October 13, 2010 10:25:48 AM
Hi all, I want to put some random think time in my script like this : ${__jexl(2000+${RANDOMNO} ) }. But it doesn't work. 2000 alone, it is working. I wonder why. Is it "cpu efficient" to use the object Gaussian Random timer or Uniform Random…

How to Retrieve all the rows from a ColumnFamily
September 27, 2010 01:52:07 AM
Hi I am new to Cassandra Database. I want to know how to Retrieve all the records from a column family, is this is different in the clustered servers vs single servers. Please suggest me with piece of code if possible. /Regards Sekhar.

Random file generation
August 16, 2010 03:07:13 PM
I am running Tomcat 6.0.26 on a Solaris 10 system. The Tomcat server is configured to listen to HTTPS communications on port 8443. When browsing to the Tomcat server remotely using the following syntax everything works as expected:…

Re: random() function does not keep value in subquery
December 30, 2010 11:48:16 AM
the hole explanation is: I modified your select a bitt get a result for the random() function sqlite> select b,a, b & a from (select random() as b, 65535 as a); -3252060536130257049|65535|48999 So I got these results b =…

random() function does not keep value in subquery
December 30, 2010 06:26:10 AM
Hi All, First post, think I have found a bug! Given the SELECT query: select l2, substr(l2,1,1), substr(l2,2,1) FROM (select substr(a,((abs(random()) % 13)*2)+1,2) AS l2 FROM (select 'abcdefghijklmnopqrstuvwxyz' as a) alpha) l2; Basically…

inconsistent use of random generators
November 16, 2010 03:16:59 PM
Hi all, Some of our algorithms do use random number generation. I quickly reviewed them and found different ways to use them. Genetic algorithm use a single static RandomGenerator shared by all instances. It default to JDKRandomGenerator and can…

Seemingly Random crashing
December 14, 2010 03:28:06 PM
Hi all, I am using mongo db behind a public website and annoyingly every couple of days it crashes and needs to be restarted. Any ideas why this might be. the log looks like Tue Dec 14 08:11:22 got kill or ctrl c or hup signal 15 (Terminated),…

Random testing feasible?
July 30, 2010 12:15:45 PM
Hi, I have a test plan with about 20 pages. I have set up 250 threads for testing. I like to know if I can perform random testing in the following manner: 1. put the urls for the 20 pages in a file 2. have 250 threads randomly go to one of the…

MapReduce for random row key subset
September 18, 2010 09:20:45 PM
Hi All, I am very new to Hadoop and Hbase and here is my first question as I was not able to get definite answer from various sources. Could you help me understand if what I am planning to do is going to fly with Hbase? Let's say I have a table…

RE: error using get_range_slice with random partitioner
August 6, 2010 05:07:01 PM
This is a multi-part message in MIME format. I ran against the 0.6 branch I still see similarly odd results. My test cases prove that set of keys have been successfully inserted, but usually I never see the first key again or I reach the first key…

Re: selecting a random subset of a view
June 28, 2010 09:25:07 AM
Thanks for all your answers I forgot to tell I try to avoid the "skip" param for performances reasons. The first method Ian suggest imply to read all my doc ids... In this case I can easily use the client random function (client is PHP). But…

RE: error using get_range_slice with random partitioner
August 6, 2010 06:06:16 PM
This is a multi-part message in MIME format. I took this approach... reject the first result of subsequent get_range_slice requests. If you look back at output I posted (below) you'll notice that not all of the 30 keys [key1...key30] get listed!…

Re: selecting a random subset of a view
June 28, 2010 02:28:22 PM
It's an error to do this if I read the doc correctly, because shows and lists functions should be idempotent. It's specified in the wiki. Mickael ----- Mail Original ----- De: "Jan Prieser" <j.prieser@hotornot.de> À: user@couchdb.apache.org…

new pseudo random number generators
September 20, 2010 05:31:42 AM
Hi all, As those subscribed to the commit list may have noticed, I have added several new pseudo-random number generators to commons-math. I should not have add such a feature without discussing on the list before. It was a wrong move and I…

RE: error using get_range_slice with random partitioner
August 9, 2010 08:59:15 AM
This is a multi-part message in MIME format. Hi Thomas, Can you share your client code for the iteration? It would probably help me catch my problem. Anyone know where in the cassandra source the integration tests are for this functionality on the…

Sharding performance decrease when using a random key
November 16, 2010 10:05:39 AM
Serial sharding key appears to be much better than random sharding key in my write-only test. MongoDB 1.6.4. Two sharding nodes, one mongos and one config server. Is it possible?

Questions and Answers are licensed under cc-wiki license.

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

3 Answers

Your answer

Join with account you already have