Hey guys,
When grabbing data out of a database I know it is possible to place them in order by a certain field either ASC or DESC ... but is it possible to give them a randomized output?
What I'm really referring to is my links page, the links at the top get seen first and therefore get the most clicks leaving the links at the bottom feeling left out. Would it be possible to randomize the entire database so there's no particular order to how each record gets placed?
TIA, Cheers.
Full Version: Random Order
Its something like this in mysql:
Just change the limit to the amount you want to display (You'll probably want to use COUNT * to get the number). I heard that there are problems if you don't use limit.
Hope that helps
CODE
SELECT * FROM table ORDER BY RAND() LIMIT 10
Just change the limit to the amount you want to display (You'll probably want to use COUNT * to get the number). I heard that there are problems if you don't use limit.
Hope that helps
Cheers Si,
What if I don't want a limit though? Would I be able to use 0 to give me a limitless amount?
What if I don't want a limit though? Would I be able to use 0 to give me a limitless amount?
http://dev.mysql.com/doc/mysql/en/mathemat...-functions.html
QUOTE
RAND() , RAND(N)
Returns a random floating-point value in the range from 0 to 1.0. If an integer argument N is specified, it is used as the seed value (producing a repeatable sequence).
mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. 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;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that is portable between platforms for the same MySQL version.
Returns a random floating-point value in the range from 0 to 1.0. If an integer argument N is specified, it is used as the seed value (producing a repeatable sequence).
mysql> SELECT RAND();
-> 0.9233482386203
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND(20);
-> 0.15888261251047
mysql> SELECT RAND();
-> 0.63553050033332
mysql> SELECT RAND();
-> 0.70100469486881
You can't use a column with RAND() values in an ORDER BY clause, because ORDER BY would evaluate the column multiple times. 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;
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
RAND() is not meant to be a perfect random generator, but instead a fast way to generate ad hoc random numbers that is portable between platforms for the same MySQL version.
In theory you could just take out the limit clause from the query but I could have sworn that I heard somewhere a while ago that there were problems using the rand function when it was returning a limitless set of results. You may want to play around with it though. If you want to do it the hard way you could use COUNT (*) to get the number fo rowns and then just set that as a limit so it would actually be limitless cause the limit is the total numbe4r of rows, so every one gets displayed! The no limit rand problem may hav ebeen sorted since I ehard about it though so have a play and let us know what happens
I just used;
Which works just fine, I think when you heard about using the LIMIT not being used would give the error came from here;
I'm going to be spending much more time at that SQL site
SQL
SELECT * FROM table_name ORDER BY RAND()
Which works just fine, I think when you heard about using the LIMIT not being used would give the error came from here;
QUOTE
Note that RAND() in a WHERE clause is re-evaluated every time the WHERE is executed.
I'm going to be spending much more time at that SQL site
Good stuff Joe, does this mean I may see the lnk to my site appearing somewhere near the top occasionally 
I think I read the thing about rand() on some guys blog a few months ago. I seem to remember it would cause some results to display more than once. It was very much a bug in mysql as opposed to a syntax error from what I remember. The syntax is fine according to the specs. It sprobab;y a bit like referencing objects in PHP, you get the code right but it still doesn't quite do what you expect!
I think I read the thing about rand() on some guys blog a few months ago. I seem to remember it would cause some results to display more than once. It was very much a bug in mysql as opposed to a syntax error from what I remember. The syntax is fine according to the specs. It sprobab;y a bit like referencing objects in PHP, you get the code right but it still doesn't quite do what you expect!
Well so far I haven't noticed any duplicate records so maybe that was a bug that was fixed
Yup Si, your link may appear at the top now sometime ... it's a much more effieicnt way to work rather than ordering by ID only.
Yup Si, your link may appear at the top now sometime ... it's a much more effieicnt way to work rather than ordering by ID only.
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.