MYSQL :: RAND Function

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

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?

RAND() With Weighted Probabilities
I don't really think this is possible, but I've had a few other posts where I've said just that, and have been pleasantly surprised in the end...

I'll be running some ads on the side of my site in an attempt to earn nominal amounts of money--ads in the plural, referring to multiple sources (e.g. Google AdSense, AdBrite, and a couple others). However, I will only be displaying ads from one source at a time. This is done rather easily, by storing the Ads HTML in a database which I've named advertisements, selecting them like this:
SELECT ad_html FROM advertisements ORDER BY RAND() LIMIT 1However, when I do that, each ad has essentially the same probability of appearing as the next. For instance, if I have a total of 5 ads saved in the database, each one has a 1/5-chance of appearing. I would like to give "weights" to each of the ads, so to speak, meaning that particular ads should show up more often than others.

The easy way to do this, of course, is to just store duplicate rows in the database, thereby naturally increasing the chances of certain ads being selected. But that seems messy, both in theory, and because it takes up more space. I'm wondering if there's any way I can add another column to my advertisements table, call it "ad_weight" (or something like that), that would be a real valued number from 0 to 1, indicating the probability that the particular row it describes will be selected.

I have absolutely no idea how to do this, nor do I know if this is really possible, so any sort of help is much appreciated! A pure SQL-query solution is definitely preferred (if it exists), although I'd be willing to do something equivalent in PHP, if that's what it takes.

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

IF Function,GROUP BY,aggregate Function Problems
Yep, I have all those problems in the title. So I'll explain each one at a time - I did have another thread relating to this very same query but I thought it was time to update where I am with the query because at the moment I feel like I am getting nowhere!

The query I have basically searches through an items_ordered table through each product and checks to see whether the item is VATable or not. This is not where I have the problem though. Where I am really having the first problem is when I am trying to use the IF function to check if the TOTAL of an order is over Â£300. IF it is then I multiply it my 0.95 (i.e. 5% off). With the query below I get no errors but neither do I get the desired result. It's as though it couldn't even see it. =....

Now() Function
I am using now function to get current date when info is added to my table. I am using a hosting provider that is 6 hrs ahead in time zone so i always have to subtract 6hrs to get the correct time. Is there anyway I can format the time to be inserted to my time zone?

Self Run Function/..
I need to delete some records from my table every 5 minutes. I thought that would be better if done via a trigger, function, etc. But I don't know how to fire a trigger every 5minutes or is there any other way to do that?

Using The AVG Function
I am trying to use this query:

SELECT average(price) FROM `data` WHERE id IN ( '3', '3', '6' )

to average the price of items users select. The problem is that when a user selects a value more than once it only includes it in the average once. How can I make it average all the values?

Looking For A Particular Function
I have a Column which consists of only DATE's and an ID code. I want to enumerate all the DATE's of a certain ID to get a grand total of a ID. Does such a function exist?

Avg Function
Why doesn't this work:

SELECT avg(value) as avg_value from playerstats where avg_value = '100' and stattype = 'r' group by avg_value

I'm trying to select the avg_value of 'r' where the avg_value = '100'.

Last Function
How do you get the last row in a grouped query. I have a table with running balances. At the end of the day, I want to find out the last balance.

select *
from accounts
group by date(dateCol)

This always gives me the first row of the grouped column dateCol.

NOW() Function
We are developing a tool here that uses a php script to insert data into a MySQL 5.0.41 database running with the INNODB engine. While tracking a bug we noticed a strange effect.

Each insert query contains a call to the NOW() function to store the create data of the record into a field automatically. Therefore we can expect in my opinion that the timestamps of the records are increasing simultaniously with the autoincrement key of the table. This actually is not the case. On the contrary the timestamps are jumping up and down with large differences. For example we have the record with ID1 that got the timestamp 14 minutes 2 seconds where ID2 got 15 minutes 15 seconds and ID3 14 minutes 9 seconds. How could this be? Does it mean that the server does not execute the statements in the order he receives them? Is it possible that it took more than a minute to execute a simple insert statement?

Max Function
I have a table with name, priority and parent as three columns...I want to get the max(priority) grouped by the parent... I am trying to get name, priority, parent, max(parent) as the four columns in the result...Could some one help me with this

select name, priority, parent from table1
select max(priority) from table1 group by parent

are the two select statements that I wish to combine...is it possible..?

Function In C
I was trying to figure out how would you write a function in MySQL using C. I saw that theres a CREATE FUNCTION syntax but am confused on how to compile and write a C function.
For example if I wanted to write a function that added an extra period after every period and called it ADD_PERIOD() how would I do that.

Example:

x = "joe runs far."
SQLSTRING = "SELECT * from Story where Sentence = '"+ADD_PERIOD(x)+"'";

this would return. joe runs far

SQL IN() Function
so i have a query such as
SELECT first_name FROM users WHERE user_ID IN (5,4,8,19,8,4) and i want the first_name to be retrieved for EACH of the cases (eg. name is repeated twice for 4 and 8)

2 Queries In 1 Function?
Is it possible to query the database twice in the same function?

If so is it possible to use results form the first query IN the second?

Count () Function
Cant anyone tell me why this very basic query won't process.
This is to give a breakdown of Mem.heard 1-12 numbers ("where did you hear about us survey") for ea of the MOrig two user types

EX:
MOrig | heardof | count_heardof

UserA 1 5 (times)
UserA 2 6 (times)
UserB 1 3 (times)
UserB 2 8 (times)

SELECT MOrig, heardof, count ( heardof ) as count_heardof
FROM Mem
GROUP BY MOrig, heardof
ORDER BY MOrig

Search-function
Does anyone happen to know how to select info from the database based on multiple strings like so:

SELECT title FROM food WHERE title LIKE '%New%' AND WHERE title LIKE '%York%' AND WHERE title LIKE '%restaurant%'

SUBSTR() Function
SELECT SUBSTR(message, 0, 20) FROM posts

My goal is to start at the first characters on the far left, and then return 20 chars only. This function for some crazy reason seems to start at the end of the string though, which is stupid, because how do I know where the string starts?

Hidden_field Function
Does mysql 4.0.24 standard have a hidden_field function? I need to maintain state from one form to another and hidden_field is used in a script I have using mysql 3.22. If 4.0.24 doesn't have hidden_field function, what can I use with mysql 4.0.24, which is used on my server?

Date Function
I have a time stamp field in my orders table and I want to form a select statement where it returns all orders with an order time of two weeks ago from the current time or later so I can keep better track of unpaid orders.

Here are the fields for the orders table
orderID
order_time
order_status
etc...

SELECT orderID FROM orders WHERE ???? AND order_status=2 ORDER BY order_time ASC

Thanks for any help. The date functions are a little confusing to me from the manual.

Function ROW_INDEX
I couldn't find a function that numbers the resultset of a query. First row should be 1, second row 2 etc.

I searched for rownum, rowcount, rowindex etc with and without underscore's.

Password() Function
I've created a simple table called users. This table contains four columns. First_Name, Last_Name, User_Name, Password. I've used the password() function when inserting a user. I have read the documentation on using other types of encryption, but it's killing me that I can't get this to work. Robert has a User_Name of "Bob" and a password of password('tomato').

===============================
HOW I ADDED BOB

insert into users values("Robert","Evans","bob",password('tomato'));

1 row added yada yada

HOW I QUERY BOB

select * from users where User_Name="bob" and password=password('tomato');

Empty Set

===============================

That exact same query is in a tutorial for a simple PHP login form, but an empty set is returned even in the command prompt using MySQL by itself, therefore the script cannot move on.

Mysql_query Function
I making a loop of the returned result set from mysql_query, it is very important to know if an update occur during the loop to the table affect the result set of the loop or not ?

I need to loop blindly without see any updates or inserts occur during the loop....

Counting Function
I have a table with a list of names and some are duplicate and I would like to count them?? It's like this:

Drew
Simon
Alli
Tim
Beth
Alli
Simon
Drew
Simon
Simon
Alli

Then to export:
Drew - 2
Simon - 4
Alli - 3
Tim - 1
Beth - 1

How could I go about asking this query?

SELECT COUNT(name) FROM table

??

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?

AES_ENCRYPT Function
Essentially I am playing around with the aes_encrypt fucntion to securly store some data in the db. The problem I am having is that I get the following error : Data truncated for column 'foo' at row 1 and the table creating was done with:

CREATE TABLE `foobar` (
`foo` varchar(10000) default NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8

with a varchar(10000) and doing and AES_ENCRYPT('foo','bar') one would think that it would work. But I suspect that I am doing something incorrectly.

Using Unix_timestamp Function
Can anyone see anything wrong with the way I am using the MySQL unix_timestamp function in this snippet from a query?

$importdata="INSERT INTO import
(date, gluc, humilog, regular, lantus, carbs)
VALUES (unix_timestamp('2004-08-01 05:04:00'),152,02.0,00.0,00.0,00.0)

The table column where I am inserting this was created as:

"date timestamp not null"

I am getting an incorrect timestamp value (20001228122240)that converts to "Mon, 18 Jan 2038 20:14:07 -0700" in PHP. As far as I can see, my usage of unix_timestamp is the same as that in the MySQL manual.

Function In Index?
I have a table that has a datetime field called (confusingly) 'date', but a lot of my queries do DATE(`date`).

I decided to add an index on DATE(`date`), but it doesn't seem to make much difference in speed. Is it valid to put an index on a function of a field?

Function Transaction
I'm working on a little framework based on SQL, i'm fighting with session becouse i'm need to check if the user is login or not and then if can see the resource or not, it's can be change for each resource. Code:

Stored Function
I managed to make a stored function 'IsEmpty' but when I use the function in a query like:
select * from tblinre where isempty('') ;

it will result in FALSE. when I use use isempty(null) it will result in TRUE.

my stored function

DELIMITER $$

DROP FUNCTION IF EXISTS `bridge`.`IsEmpty` $$
CREATE FUNCTION `IsEmpty`(p VARCHAR(1)) RETURNS tinyint(1)
DETERMINISTIC
BEGIN
RETURN IF(TRIM(p)='' OR p IS NULL,TRUE,FALSE);
END $$

DELIMITER ;

CASE Function
The following excerpt from an SQL Script outputs a table containing a column entitled "TypTitle" containing rows of PASS and FAIL.

Is there a way to change the output column name from "TypTitle" to "RESULT" without changing the database? Code:

DateDiff() Function
I am trying to use the MSSQL function DateDiff('m', date1, date2) in MySQL but mysql do not support the type 'm' or 'd' or 'yyyy'. I do not want to receive the datediff value as a SELECT field e.g.

"SELECT datediff('m',dateofbirth,'2005/06/23') from Dependant WHERE TranID = 3068"

CONCAT Function (bug?)
I'm needed to insert large BLOBs into a database. With the 1MB packet
limit, sending larger amounts of data would be difficult, so I had a neat
idea. I would do an initial insert of an empty record and get the
auto_insert ID from the response, and then loop through, appending data to
the record.

My table is simple. One unsigned int auto_increment field (DataID), and
one long blob field (BinaryData).

When I loop through the data to send, I run:

UPDATE BinaryTable SET BinaryData=CONCAT(BinaryData, 'My binary data
here') WHERE DataID = 35

The binary data I insert I escape null characters, backslashes, single and
double quotes. The data seems to insert fine.

The problem is that as I increase the amount of data in the field, CONCAT
seems to drop all but the last 416k of the data. Thus if I loop through
adding 400k blocks at a time (Which I do) I am left with at most 800k of
data in the blob field.

Using 4k blocks I end up with 419k of data in the field when all is said
and done.

Mysql Function
i am hosting my website on a hosting website server. I made Mysql database there. I noticed one thing when i insert data in tables they have a colum called "function" with drop down box where they have values like ASCII,CHAR,SOUNDEX, UCASE, LCASE, COUNT,AVG any many other.

I don't pick any of them. But I am just wondering if I need to pick either ASCII or char?

Function Abs Doesn't Exist
I nstalled a mysql 5.0 Database on one of my servers but I receive the following error when I want to use a function from MySQL.

create view myView AS
select DATA,TIMED from myTable where ( abs (1138612032902 - myTable.TIMED)*11) >10 ;

And I receive :
ERROR 1305 (42000): FUNCTION MYDB.abs does not exist

I want to know if somebody has faced this error and I appreciate any solutions for this problem.