MYSQL :: Random Select

Select At Random?
I have a db that I'm using as a mailing list. In many (but not all) cases, there are several contacts per company. I rarely want to send my mail to more than 1 person at any one company, and I never want to send to more than 3.

I don't have any criteria that I can use to select which contact to select, so I want to do it essentially at random. So I want to contruct a SELECT query that says, "when there are >1 contact at company X, pick 1 (or 2) of them at random. When there is only 1 contact at a company, include that contact/company in the result as well."

I'm sure there's an easy way to do this, but having searched online and read a lot of the 1500-page manual, I haven't figured it out.

Select Random Row
I have read that

"select * from table_name order by rand()" will gives poor performance when there are thousands of rows in the table. Does the same holds true even if there is a where condition (returns only hundres of rows at most)?
For example:

"select * from table_name where col1=2 order by rand()". If I have thousands of rows in the table but the where condition limit it down to couple of hundred, will this query still gives bad performance?

Select N Random Strings?
I want to populate a table with some number of random strings. How about that? I'm just running through the manual and can't find anything.

Weighted Random Select
I'm trying to retrieve a "random" row from a table where the randomness is "weighted" by a factor, let's call it "weight" (original, huh?).

the following (pseude-SQL) works but is EXTREMELY slow:

SELECT * FROM table
ORDER BY RAND() * ( 1 / weight)
LIMIT 1;

I've read that "ORDER BY RAND()" is a big no-no as far as optimization is concerned.

Is there another, more optimal way to construct a weighted random query?

Random Order Select
I have a user database which I use for meetings. What it does is it creates notes for everyone thats going to be in the meeting. Right now, the order people give notes is dependant on the order the select statement returns (right now I have it as desc using the ID#). My boss would like it if it could "randomly" arrange the users. i.e

Day 1:
1 - Bob
3 - John
2 - Beth
5 - Susie
....

Day 2:
5 - Susie
2 - Beth
1 - Bob
3 - John
....

etc.. So no one feels like they are being picked on or it doesn't get stagnant.

What would be nice would be "Select blah blah Order by Random" but I don't think thats an option. Is there anything in mysql to do this or is this something I'm going to have to manipulate after I have the data out of the database?

Select Random Entry
I have 3 seperate tables of people who have entered a competition. What I need to do now is select the 1 random winner from the 3 tables.
So is there any way I can do this in mysql or am I gonna need to write a script?

Random Number Select
The first request
Is there a way to randomly select a (Number out from the Primary Key, which are only numbers) to display with all of the other fields info along with it.
Basically, I'm trying to do a random select using the primary key, from the mysql database. Using only the numbers that are stored in a primary key field, in a table which consist of about 25000 rows.
The first request is primary. The second request:
Would be that the random select would be done 3 times, each number selected would be eliminated from being selected after each time. So that there wouldn't be any duplicate numbers randomly selected in the final process.

PHP Select Random SQL Entry
I have tried this:Code:

mysql_query("SELECT * FROM users ORDER BY 'rand(0,999999)' LIMIT 1");

it doesnt select a random one, it just selects the same one over and over again.
i have also tried:

Code:

mysql_query("SELECT * FROM users ORDER BY 'rand()' LIMIT 1");

Efficient Select Random From Large Table
I have a table with over 4 million rows. I want to randomly select one row. The following works: select * from items order by rand() limit 1;

Random Rows, Non-random Ordering
Normally to select random rows from a table I'd do something like:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY random
LIMIT 5
Unfortunately, this time I need 5 random rows sorted on a different field:

SELECT field1, field2, RAND() AS random
FROM mytable
ORDER BY field2
LIMIT 5
Is there a way that I can still sort by field2 and yet still select 5 rows at random? Could the former query be run as a subquery and then a second ORDER BY clause run on those results? Worst-case I can re-sort the results in my host language, but a pure SQL solution would be best I think.

Bonus points if the answer can always put a particular row first in the results.

Sql Random Pwd
i read somewhere that it is not safe to use a mysql randomly generated password . I have a table with many records and want to reset everyone's password to a random alpha-numeric password.
What is the best way to do this one ? something using a php random pwd and then update the table ? or there is something better than that ?

Random Row
I am trying to get one random row at a time. Right now I am using this SQL:

Code:

SELECT * FROM `trtrack` ORDER BY RAND( ) LIMIT 0 , 1

But it's really slow, some queries taking up to .5 to 1 second. Is there any other way? I use PHP.

Add A Random Value
in php, this would simply be rand(1,5)
in mysql, I tried

update `table` set column=(column+rand(1,5));

but that was not a correct guess.
how can I add a random value of 1 digit bt x and y?

Random Keyword
I have a question about what query I should use to achieve the following...

I have a table called 'keywords' that has the following fields

id (auto_increment)
pagename
keyword

There can be multiple keywords for one pagename, but each keyword has it's own row. For example, this could be in the table:

id pagename keyword
11 test-news test
10 test-news news
9 test-news article
What I want to achieve is to select 1 random keyword for each unique pagename. So there can only be 1 keyword selected for a pagename, but it should happen for all unique pagename entries. What query would I use to achieve this?

Random Retrieving
data in myTable

(id) name
(1) Tom
(4) Jane
(8) John
I have data in myTable like the above.

Can I retrieve one of names in name column randomly?

Random Images
is there sombody who can helpme witht he following, i am ''pretty new'' to
php and am looking for a script to show images randomly at a 10/ 15 seconds
interval.

Random Query
I have been tasked with getting daily quotes out of a sql database, there needs to be 3 that are randomly selected. I have not found any documentation in regards to doing this.

How would I qurey a database and pull 3 quotes of the day out randomly. This should even occur when a user refreshes the page, is there a sql function that would do this? or do I need to do it via php, perl, etc...

Random Query
$querystring = $querystring." ORDER BY status, dateposted desc";

How can i make my query that its sorts first by status and then random for date posted.

Random Function?
I would like to know if there is an existing random function I can use to generate a random number so I can concatenate it to a string?

Inserting Random #
I have a db name image records. It has 3 columns recno_no, file_name and active_fl.

is there a way to insert 100 random #'s into the database table and its columns for testing?

Random Records
I'm trying to get a random record for use with my website. I've tried figuring out some premade code and it's led me nowhere..

Here's basically the rundown...

I have a database setup by my site's shopping cart system, and in it are 2 tables i need to read from, "specials" and "featured". I want to pull 4 random entries from "specials" and 1 from "featured" to have posted on my site's main page outside of the store.

I know how to connect to and select the database, I even know how to send queries, but the whole selecting a random entry is what's got me at a brick wall.

I'm not used to PHP and MySQL, i'm just now learning after 4 years of using Perl(and never using a database system, but creating my own database-like files).

Order By Random
Is there anyway to order records randomly? instead of order by id desc to order it randomly??

ORDER BY Random
Is it possible to pull results from a database using a SELECT... ORDER BY and pull them in a random order?

Random Pick
When an artist isn't picked, there is a random artist picked. Now I have it setup so it counts the rows, and picks a random number from 1-nr of rows. This number is then used as the id and gotten a mysql query from this id.

$query = mysql_query("SELECT * FROM `artists`");
$total = mysql_num_rows($query);
$random=rand(1,$total);
$right = mysql_query("SELECT * FROM `artists` WHERE `id`=$random");

Now, I want something a bit different. I also have ABC....Z, by the first letterof Artist Name. If the first letter is picked, but no artist, I want the random artist to be start with this letter. I wanted to use something like above but the id won't be in order. it could be like 1, 20, 32, etc etc.

Random Data
I keep getting random data put in to by database??? Any idea how to stop this?

Random Row Order
What do I need to put into a SQL statement for it to retrieve 100 Random Rows (containing certain criteria in the WHERE clause). Something like this (theoretically) is what i'm looking for:

SELECT *
FROM table
WHERE field="yes"
ORDER RANDOM
LIMIT 0, 25

Any Ideas?

Random Ports
mysql 5.0 was working fine in Debian "etch".

Then I installed awstats, which changed something somewhere and now the server starts on a random port. However, no clients can FIND it! How do I resolve this issue?

Generating Random
I have a database set up and all I need to do is make a field with just something random for each entry that is already in the database. Something just 5 numbers and/or letters is fine. How would I go about doing this?

Selecting Random
so i have a table with three columns: sentence, auto_id and createdate (datetime). I want to display random sentences (from sentence) up to 15 from each day. That's easy enough:
$query = "SELECT sentence FROM sentences WHERE createdate LIKE '$today%' ORDER BY RAND() limit 15";
but now I'm running into a problem and that is if a user submits a huuuge block of sentences into this db I end up displaying mostly that users stuff (and often only that users stuff) so I'd like to come up with a query that will use the information afforded it to select:only one or two randomly selected sentence(s) from each entry and return it randomly up to 15.
what does that query look like?

Random Insert
Has anyone ever experience mysql randomly entering blank data into without an INSERT?
I have a login/sigup system that seems to insert blank data into a table w/o anyone actually signing up.

It only happens to 2 tables when in the process of the signup writes to 5 tables.

What really gets me is that the insert date is exactly the same every time this happens.

12-31-69 04:00:00 PM

What is also interesting is that the .cgi form does not allow any blank fields.

Random Record
I have a table that has several hundered rows with one column being "LastRated", a DATETIME field.
Currently I am pulling a random record from this table with:

SELECT * FROM tblImages ORDER BY Rand() LIMIT 1

Is there anyway I can re-work this query so that it is much more likely to pick on of the records with an older LastRated?Even if it were to only randomly pull a record from the 50 oldest LastRateds that would be fine.

Auto_Increment At Random
im using the MySQL Control Panel 0.9.4. I can do this with SQL so I assume MySQL can as well?:

I want to have a user fill out a form on my site, then when their information is inserted into the database I want it to automatically generate the User ID #. I went ahead and set this up and checked, â€œAuto_Incrementâ€. And it works. The only problem is that it increases the increment by 1 each time. I have a Length of 11 and want it to randomly mix up all the numbers. I want to avoid a member changing the 1 increment to access someone elseâ€™s account. Is there a way to have the number generate at random rather than by 1 increment at a time in MySQL?

Random Selections
I am trying to select a certain number of records out of a table. The catch is that I want the records to be chosen at random. Is there a way in mysql that would allow me to a random selection?

Checking For Random ID
Let's say that I generate a randon id using this method (if there is a better one, please post it):

$id = sha1(mt_rand(1, 500));

If it is crucial to make sure that this does not exist in the database (let's say I need a million of these in my database, and not a single one can be the same as another), how would I check that?

Do I just retrieve all the ids from the table and compare them to the newly generated one? What is the impact on resources/speed?

Random Order
Suppose I have a table with 10 records in it.
I can select them with either ascending or desending order. Is it possible that I can select them in random order, so sequences is different each time?

Random Output
is there any way to do a mysql RANDOM output? Like e.g. if I got a table and I want to have every row displayed of the table, but by random (changing whenever you look at it), but every entry being displayed once

Random Selection
Basically I have this:

(category INT,type INT,item TEXT)

I want to be able to select (say) 20 distinct random records with category=x and type=y.
How can I do that ?

Random Rows
I understand the preferred way to select random rows is as follows:
SELECT * FROM tbl_name ORDER BY RAND();
my problem is, I also need "order by" to sort the results in alphabetical order. how can I do both?
in other words, let's say I have 200 rows. I want 30 randomly selected, but I want those 30 alphabetised.

Random Index
Is there a way to display search results in random order?

Random Number
I have a list of email addresses in a text file.
I want to create a table and have them added to a new table with 3 columns/fields - ID, Email, and UID

ID - Integer - auto increment
Email - varchar 40
UID - Unique random value 15 characters (varchar)

How do I get the UID added such that it is random and unique.
Can MySQL do this?

Random Records
Im told that ORDER by rand() isnt a good idea to use on large tables, so Im interested to know how else this could be done. I need to display 20 random records (there are conditions in the WHERE clause as well).

Random Join
What is the best way to randomly join 2 tables?

table "users" contains a list of unique User IDs.

table other_user_data contains a list of User IDs (and other fields) that do not match the user IDs in the first table. There can be multiple rows per user ID in other_user_data.I would like that every time i run the query, the users in the users table should be matched with any other user in the second table and return users.user_id and multiple rows from other_user_data that "match".

Random Topic
www.habbomedia.ca/topic. I made that using php and html but what i want to do is add theinto different tables, one english and one chinese, etc... and they are acessed by a php file example english.php, that reads only the english table's words and displays a random on upon each refresh.... is this possible?

Rows Disappearing At Random
I've just confirmed that rows in my MySQL database are disappearing at random. It seems that the larger my tables get, the more often rows disappear. I am running MySQL 5.0.27-standard-log. How can I prevent this?

How To Get 5 Random Returns From Mysql?
I have a table that stores username and image_id. Like -

user_name | image_id

user_1 | image_1
user_2 | image_2
user_3 | image_33
user_2 | image_56 etc.

I need to get 5 random image_ids, each from a different user. I was considering using rand(), but google told me that this will slow down the sql server, bigger the database.

PHP

SELECT DISTINCT user_name, image_id FROM tablename ORDER BY RAND() LIMIT 5

Generate Random String
I'm writing a sequence of insert statements which I want to execute via the phpmyadmin interface to insert records into a table.

The table contains only two columns: name and uid. The first column is obvious. The second column is just a random string of 32 characters.

My insert statement are of the form ...

INSERT INTO user_table (name, uid) VALUES('Some Body', &#55616;&#56537;dfdfkdsfjdfddf33dfkdfk');
INSERT INTO user_table (name, uid) VALUES('Another Body', 'ewrkdk32832wrekldfnejdsfdsf');

I want to know if there is an easier way write the insert statement so that the statement will generate the random 32 character string for me.

Such as something of the form....

INSERT INTO user_table (name, uid) VALUES('Some Body', RANDOM(32));

Or is there a way to tell phpmyadmin to populate the "uid" column of existing records with a random string if that column contains NULL.

Generating A Random 'Tip Of The Day'
I've recently been playing around creating a Google gadget with a kind of 'Tip of the Day' theme.

At the moment it gets a different random tip from the database every time the page is refreshed. What I want to do is set it so it will show the same tip for 24 hours and then change, so it will take longer to run out of original tips

I'm just wondering the best way to do this. Should I write a daily cron job to fetch a random tip and send it to a different table, or to generate the page dynamically? Or is there a better way to do it using MySQL? Have a date field in the tips database and allocate each tip to an upcoming day? Or do it with PHP somehow? Or even cookies, if it comes to that?

Random Select

When I do a select with a limit I get always the same records. Usually this is the desidered effect. But now I want to do a random select. So every time I do a select I should get a different set of results. What is the best way to achieve this effect?

When I do a select with a limit I get always the same records. Usually this
is the desidered effect. But now I want to do a random select. So every time I do a select I should get a different set of results. What is the best way to achieve this effect?