Choose randomly x out of y records

theraj

Hi,

Am using PHP and MySql to develop a website. Am having difficulty finding suitable SQL or PHP script to choose x records randomly out of y records.

e.g. Choosing 2 records randomly out of 5. Records would be in Mysql:

Example:

Records 1,2,3,4,5.

e.g.1: 3,5
e.g.2: 1,4
e.g.3: 2,3

Thanks in advance,

Raj

devinemke

SELECT * FROM table ORDER BY RAND() LIMIT 2

theraj

Ideally I'd like to pick only x out of y records. Is that possible?

devinemke

how are you defining "y"? my example picks 2 random records from "table" (however many records are in "table"). you say "only x out of y records". but what criteria are you narrowing your query down to only the "y" range of records?

theraj

What I mean is is there a way of directly choosing 2 random records out of the database?

rather than

choosing all of the records from the database and then choosing 2 random records from that.

I think I'm avoiding CPU overhead my way (if there is a way).

Raj

devinemke

Originally posted by theraj
What I mean is is there a way of directly choosing 2 random records out of the database?

that's precisely what my original example does.

theraj

I think your solution "SELECT * FROM table ORDER BY RAND() LIMIT 2" says:

A)
1) Choose all the records
2) Order the records randomly
3) Choose the first 2 of these randomly ordered records

is there a way to do the following? :

😎
1) Choose a random record
2) Choose another random record (but not the same as the first)

If there is a way to do B does this require less processing power?

Raj

devinemke

from the mySQL manual:

As of MySQL 3.23, you can retrieve rows in random order like this:

mysql> SELECT * FROM tbl_name ORDER BY RAND();

ORDER BY RAND() combined with LIMIT is useful for selecting a random sample of a set of rows:

mysql> SELECT * FROM table1, table2 WHERE a=b AND c<d ORDER BY RAND() LIMIT 1000;

ajdegans

I dont get you guys, some one asks. Devinemke gives a possible sollution...

WHY all the fuzzing whether its working or not. Just try it.... The code works for me alright!

Why not just be thankfull for all those people who are willing to help you (and me) out of our coding problems! And for free too!😃

Roger_Ramjet

Raj, try learning how DBMS work. They are not flat file processing systems as you, and so many newbies seem to think. Every problem that you can think of has been addressed in the 30 years that relational database management systems have been around. Of course your interpretation of the processing involves massive wasted resources, which is why RDBMS do NOT WORK THAT WAY. They are optimised to return results with the least possible expenditure of resources. In tech speak RTFM (read the flipping manual).

ajdegans

I think we should thank all the coders that are willing to help none the less its helpfull or not what they do for you!

Imagine it was you, helping people on forums, imagine how much time that could cost you... And then they get shit like this? BLAH!

yes yes, i know i fail to say 'thanks' too alot but atleast im not fuzzing on things like this!

Roger_Ramjet

ajedegans, I hope that you did not feel that I was giving you shit. I was being a bit heavy on theraj, I know, but not on you.

ajdegans

i know :evilgrin: