MYSQL :: RAND() With Weighted Probabilities

Weighted Probability
What I am trying to do is choose 5 random rows from a table, but give them a random weight for the probability of them showing.
In other words, I want tables with column 'weight' that have a value of, say, 3 to have a 3x greater chance of being chosen when the random query is being executed than rows with a 'weight' column of value 1.

Weighted Averages
Here's a pickle that has my department stumped.
We have a table with the following fileds:

1. True APR
2. Annualized Unexpired Loan Value (A.U.L)

Now what I need to do is to multiply the True APR by the A.U.L to get the weight.
Then I need to total the weights and total the A.U.L. to come up with a weighted average.
Now the first part is easy, but it's the totalling that I'm having difficulty with. Please tell me how to finish this statement:

SELECT true_apr, aul, (true_apr * aul) as weight, (need total statements here) FROM table WHERE this = 'this'

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?

Rand()?
I use this query to return the three fields of all providers in my database.

PHP

$result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY ID ASC LIMIT $from, $max_results ");

I'd like to instead return a random list of the providers, how do I do that?
(like this?)

PHP

$result = mysql_query(" SELECT Approve, ID, Name FROM PROVIDER WHERE (CURRENT_DATE BETWEEN From_Date AND To_Date OR From_Date IS NULL) AND Approve = Ƈ' ORDER BY rand(ID) LIMIT $from, $max_results ");

FIRST() And RAND()
I have two tables:

Sport (Id, Name)
SportPhoto (Id, SportId, Active) (the actual photos are stored elsewhere...)

I want a VIEW and/or PROCEDURE that will get a the list of sports with a random photo for each. I need something like this:

SELECT FIRST(Id ORDER BY RAND()) Id, SportId
FROM SportPhoto
WHERE ACTIVE = 1
GROUP BY SportId;

where that would get the list of sports with one random photo for each sport. I guess there's no FIRST() aggregate function in MySQL. So, how would I do what I need to do?

Order By If(rand()....)
I'm trying to return a list of products, where the top of the list will be one of 2 products, the choice being randomly selected. I tried a rand() condition in the ORDER BY clause, which partly works, but of course the rand() function is evaluated for every row, so it doesn't give the consistent results i need.

SELECT productID, prodName, adMessage, nPrice, nPrice*(1+vatRate) as salePrice, showSale, originalPrice, pic.picType, pic.ordering
FROM tProducts p
JOIN pictures pic ON p.productID=pic.relProdId and pic.picExists = 1
JOIN taxCodes tax ON p.taxCode=tax.taxCode
WHERE p.featuredProduct=1
AND p.stocklevel>-3
GROUP BY p.productID
ORDER BY productID=if(rand()<0.5,117029,117035 ) desc, rand()
LIMIT 10

RAND Function
Basically, I have a stored function that is designed to deliver a random 3 digit code. But I need it to be of the format #~#~# (ie, 3~0~7, 5~1~5, etc) So I set up the function to generate 3 random digits and then concatenate them together, using the following lines inside the function:

SET v1 = FLOOR(RAND() * 10);
SET v2 = FLOOR(RAND() * 10);
SET v3 = FLOOR(RAND() * 10);
SET vCode = CONCAT_WS('~', v1, v2, v3);

The function works fine, and gives me back my 3 digit code in the exact format that I want.

However, after testing it out, I've discovered that it isn't entirely random. There are certain combinations that I never end up getting returned from the function. There are basically 1000 different possible combinations, but I'm only ever receiving 800 combinations back. I've loop tested the function to generate literally millions and millions of different codes, and there are certain codes that I just never see. Code:

Using Rand() Within Categories
Supposed the database looks like this: image_id, category_id, description, and for each category_id a, b, c, d, e, and f (6 categories), there are 5 images (5 image_ids). And i would like to select randomly one of the 5 images from each category. the result of the query should have 6 rows with images with the different categories, eg:

image_id / category_id / description
2 / a / test
7 / b / test
12 / c / test
29 / d / test
53 / e / test
77 / f / test

is it possible to do this?

ORDER BY RAND()
RedTable(name TEXT,newness INT)

I want to select random records but at the same time select those with the highest 'newness' value. So if the highest newness value in the table is 5 and ten records have that value I want to select a random one from those ten, but if all the records have the same 'newness' I want a completely random one.

Can I do this: "SELECT name FROM RedTable ORDER BY newness,RAND() LIMIT 1"

I don't know if that will really work. It's important that I maintain randomness so I really need to be sure.

Rand() Not Working In Php?
I'm trying to get the rand from the mysql. When I use the MySQL Front software (SELECT * FROM keyword ORDER BY RAND() LIMIT 2, it gives me random which is working fine.

But when I use the php and it doesn't give me random.
What wrong with php?

$query = "SELECT * FROM keyword ORDER BY rand() LIMIT 2";
$result = mysql_query($query) or die("Query failed : " . mysql_error());

echo "<PRE>";
while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {
print_r($line);
}
echo "</pre>";
mysql_free_result($result);

Rand() Question
hey guys i need mutiple images on one page in random use.
i have this

$QQQ1 = mysql_query("SELECT
*
FROM
supps AS t1
INNER JOIN
(
SELECT
ROUND(RAND() * (SELECT MAX(id) FROM supps)) AS id
) AS t2
ON
t1.id >= t2.id
ORDER BY
t1.id ASC
LIMIT 1; ");

Optimize Rand() On A Big Database
I am using RAND() and I know it is killing my MySQL performance. My database is currently at 200K rows and growing quickly. Here is a general mysql SELECT that is looped about ten times depending on the page.

PHP

if($drange == "thisweek") {
$subquery = "SELECT articles.title, articles.link,
articles.date, site.site_name
FROM articles,feeds,site
WHERE articles.article_id != $articleid AND
(week(articles.date)=week(now()) AND
year(articles.date)=year(now())) AND
articles.feed_id=feeds.feed_id AND
feeds.sub_id=$subid AND feeds.site_id = site.site_id
ORDER BY RAND() LIMIT 3";
}

I am trying to optimize this to NOT use RAND(). For this query, it is looking at all the latest additions over the past week. I have thought about running this query only selecting the IDs of the rows, then randomly selecting 3 numbers out of the selection and running the query again just to select the rows. But would that be faster?

Trying To Understand RAND() And LIMIT
I have been doing some queries using a combination of factors that include RAND() and LIMIT.

My code looks like this:

ORDER BY RAND() Same Every Time
I'm trying to get a random record from a table, but the overwhelmingly recommended method - "SELECT * FROM table ORDER BY RAND() LIMIT 1" - gives me the same record every time. When I try the query without the LIMIT clause, I can see that the records are indeed in an order that cannot be explained as anything but random (i.e. not the order I entered them, and not the order of the primary key), but when I run the query again they repeatedly show up in the same "random" order. Is there something I need to do to seed it so that my results change? I see no mention of this anywhere - everyone seems to act as if this query will simply work.

Algortihm Used In Order By Rand()
Does anyone know where I can find any documentation and/or information
about the algorithm used in ORDER BY RAND() in mysql ?

Group By And Order By Rand()
I have the following data in a table:

| art_id | artist_id | category_id | title |
| 1 | 1 | 1 |A Golden Day For the Guardians of the Earth|
| 3 | 1 | 1 |Blizzard Along Bear Tooth Range|
| 9 | 1 | 2 |Spring of the Rainbow Warriors|
| 8 | 2 | 5 |Evening at Rock Island|
| 5 | 2 | 2 |Snowy March Evening|
| 2 | 2 | 7 |Afternoon Missouri River|
| 4 | 3 | 1 |Pelican King|
| 6 | 4 | 7 |Moose Cabin|

I am trying to select a random art_id , but I only want 1 id per category. I tried:

select art_id,artist_id,category_id, title from art group by category_id order by rand();

but it always gives the same art_ids, just in a different order, so I always get art_id 1 for category 1, never id 3 or 4.

Group By / Rand Problem
I try this sql, but it don't work :(
Have you a solution ?

----------------------------------------------------------------------
SELECT
galleries.id_gallerie as lid ,
(
SELECT
id_contenu
FROM
contenus
WHERE
id_gallerie=lid
ORDER BY
RAND()
LIMIT 0,1
)
as lid_contenu
FROM
galleries
WHERE
galleries.home=1 AND
galleries.online=1
------------------------------------------------------------------------------

Order By Rand() Problem
this query works fine in phpmyadmin:

SELECT name, id
FROM `raffaello_ties`
ORDER BY rand( )
LIMIT 0 , 30

when I try to do the same in php, I get an error, although I don't know why. here's the error:

You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'order by rand() limit 0, 30' at line 1.

Faster RAND() Query
I need to select from my db table ONE (1) random row, just one. So, I am doing this:

SELECT categories.cat_name, subcategories.sub_name, map.faq
FROM categories, subcategories, map
WHERE categories.cat_id=map.cat AND subcategories.sub_id=map.sub
ORDER BY RAND() LIMIT 1"

categories=10 rows
subcategories=20 rows
map=100 rows