MYSQL :: Algortihm Used In Order By Rand()

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

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.

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.

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.

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.

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

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?

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?

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:

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
------------------------------------------------------------------------------

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

ORDER BY WHERE + Include Rest Of Table Or ORDER? Possible?
I need to create some SQL that when run returns all the rows which a column is equal to number that has been specified, but then the query needs to return the rest of the rows in the table. How could this be done? is it even possible? Is their order syntax that could do this better?

Multiple ORDER & SUB ORDER On The Same MySQL Table
I am trying to do multiple ORDERS or SUB ORDERS on the same MySQL table, and I'm loosing my mind trying to fathom the logic and SQL statement to use, I'm no MySQL genius! more a newbie.

*** SEE ATTACHED IMAGE PLEASE

I have tried all sorts of SQL statements, e.g:

SELECT * FROM categories GROUP BY parent ORDER BY order, parent DESC

Nothing seems to work.

I think my only solution is to do a bubble-sort after putting the whole table in a PHP ARRAY ?

I'd also like the menu to act like the + pop-open sub-menu boxes on the Forum left column menu.

How To ORDER BY DESC Properly. It Makes 150 Before 50 If ASC Order!?
I have this list:
50
150
200
250
300
350
400
450
500

If I order it by ASC order then mysql results this:
150
200
250
300
350
400
450
50
500

What the hell ?? How do I order it properly?

How Does MySQL Order Records With No ORDER BY?
If you don't specify an ORDER BY clause, how does MySQL order the records? Is it just the order in which they found in the database?

Selection Order Without Order By!?
I have a table with this structure

trans
-------
t_no (int auto_increment)
t_timestamp (datetime)
t_price (double)
t_vol (int)

I've inserted two record into trans table with same t_timestamp value. When i do a select * from trans it doesn't always return ascending order based on t_no? But sometimes it does?

I would like to know how MySql order selection result wihtout the order by command based on this scenario.

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they live. In the comments they are also allowed to put text, that might contain a county listing also. I want to have the results put the records that have hcounty matches first, then the records that have a county match in the comments field after the first set of records. I have tried all sorts of order by and group by and can't get the result I'm looking for....

Order By - Order Of Precedence?
I know how to use the order by, but wondered if there is an order of
precedence in using it.

Facts: hcounty is a county that someone puts in a home county in which they
live. In the comments they are also
allowed to put text, that might contain a county listing also. I want to
have the results put the records
that have hcounty matches first, then the records that have a county match
in the comments field after the first set of records. I have tried all sorts
of order by and group by and can't get the result I'm looking for. Any hint
or assistance would be greatly appreciated.

$query = "SELECT * FROM contacts WHERE hcounty ='$searchcounty' or comments
like '%$searchcounty%' order by state, hcounty, dtestart ";

Force Order By Order??
I have 6 rows of data... each has a name, eg: c1, c2, mrgs1, totalfte.
they are all under the one column "field". Is there a way I can force their order??

at the mo, I use "ORDER BY field DESC" and its coming out lile

mrgs1, totalfte, c1, c2

I need

totalfte, mrgs1, c1, c2

is it possible to sort like this??

Custom Order For ORDER BY
I'm grabbing six specific records, like so:

$result = mysql_query("SELECT * FROM casestudies WHERE id=1 OR id=2 OR id=3 OR id=4 OR id=5 OR id=6 ORDER BY ___________",$db);

I'd like to post them in a custom order, specifically: 1,3,4,5,6,2. Can't figure out how to do this with ORDER BY, if that's even possible. Anything I'm missing?

ORDER BY Out Of Order - Fixed
I have a query that uses two tables that I want to produce output like the following:

+-------------+-------------+-----------+
| LOCATION | GROSS SALES | NET SALES |
+-------------+-------------+-----------+
| Location 1 | 11,860,735 | 2,907,552 |
| Location 4 | 4,814,029 | 1,077,003 |
| Location 3 | 2,711,795 | 710,804 |
| Location 5 | 2,660,040 | 666,255 |
| Location 2 | 2,049,470 | 563,830 |
| Location 8 | 2,227,730 | 543,220 |
| Location 7 | 1,766,880 | 425,483 |
| Location 6 | 1,721,681 | 367,252 |
| Location 10 | 13,424 | 2,253 |
+-------------+-------------+-----------+

However, I cannot get the "order by" part of the statement to work. I have tried adding a zero to the order by alias. And when I attempt to specify the field in the order by with the calculation and not the alias I get an error.

The following examples are queries without the zero and with a zero added to the alias in the order by line. I have also added the details for the table below for reference. Code:

Order By A Sum?
Ok this is really hard to explain. Table1 has userid's that correspond to another table along with 2 other fields num1 and num2. I need to get the 15 userids from Table1 with the highest SUM(num1) / SUM(num2). I can do this with PHP but would like to do it solely using SQL.

Help With ORDER BY
I have this query:

PHP

SELECT p.*, u.points, c.Title AS category, c.CategoryID AS cid, CONCAT(u.FirstName,' ',u.LastName) AS user,
u.UserID AS uid, COUNT(b.UserID) AS referrals
FROM products AS p, categories AS c, ltd_user AS u
LEFT JOIN ltd_user b on p.UserID=b.referrer
WHERE p.section=c.CategoryID AND u.UserID=p.UserID AND p.pppicture != '' AND b.ConfirmS = 1 AND b.UniqueIP = 1 and p.power=1
AND u.points>=10 AND p.section IN (1,2,3,4,5,8,9,10,11,12,13)
GROUP BY p.productid
ORDER BY p.bid DESC, referrals DESC LIMIT 0,10

problem is that query order only by p.bid while I need to sort by both

Order By
I'm trying to modify a mysql search and am coming up against a brick wall due to my lack of mysql knowlwedge.

Basically the search is for an online dating site. It references a table containing the basic member details and also a table containing member profile details. The member profile table includes a lastlogin column so I'm currently ordering search results by the most recent member login. However, I'd like members who have bothered to upload photos as well to be found above those without photos. The photos are saved into a separate table which includes a field called member_id (the same field can be found in the other 2 tables mentioned aswell).

Currently the order by part of the query is:

ORDER BY p.lastlogin DESC
What do I need to add to this to ensure members with photos are also listed before those without?

Order By?
The following mysql statement produces a buch of unordered results:
SELECT Approve, ID, Name, Image1 FROM PROVIDER WHERE Approve = Ƈ' LIMIT $from, $max_results;
3
1
5
7
4
6
2
Id like to place an ORDER BY thing in there (ORDER BY ID(asc))
so it appears like this;
1
2
3
4
5
6
7
How do I do that?

Order By
If i have a table with say 50 rows,
is it possible to display the data of the first 20 rows
by, as an example...ORDER BY age
and the remaining 30 rows
by, as an example...ORDER BY name

ORDER BY
The following query does not seem to order by "bid_amount". It will simply take the first entry in the database.

Any idea how to make it order by "bid_amount" (DESC)? ......

ORDER BY A Set
I want to order a result set by a specific order, rather than just ascending or descending alphabetically/numerically. For example if I have a table called region

idName
1London
2Scotland
3Wales
4Midlands
..
..
..

but I want them ordered so that the result is Scotland, London, Wales,
Midlands,... can I use some sort of set in the order by clause?

Something like:
select name from region order by id {2,1,3,4};

I haven't used mysql SETs before so not sure if they can be applied here.

ORDER BY
I have dates recorded in my database for my website, in a var char in this format: 4/8/07, 4/21/07, 4/22/07, 4/10/07, etc. When I use order by to try to order by the most recent date, it doesn't work.

I even know why, and it's because it'll put 4/8/07 before 4/21/07 because there is no 0 before the 8, so it sorts it as 8 larger than 2, which it is. Is there any way to get around this, so it will order correctly?

Order By
I'm doing an ORDER BY date DESC and I want 10:00:00 to be higher that 4:00:00, so why isn't it?

Order
I have a mysql table with a column with a few zero values and an ordered list of numbers.. eg 0,0,0,1,2,3,4,5

is there a way to order the table in ascending order for the numbers 1-5 then display the ones with zero afterwards in one sql query..

so it would turn out like 1,2,3,4,5,0,0,0

ORDER BY
The column duration in the reports table is type = time. The following query produces the results below it. My question is why does the 9th entry appear where it does? Or better yet, How can I get it to sort "correctly?" That's "correctly" by my understanding, not necessarily SQL's. :)

SELECT a.pilot_num AS Pilot,
sec_to_time(sum(time_to_sec(b.duration))) AS Duration
FROM pilots AS a, reports AS b
WHERE a.pilot_id = b.pilot_id AND month(now())=month(b.date) AND year(now()) = year(b.date)
GROUP BY Pilot
ORDER BY Duration DESC
LIMIT 10

Pilot Duration
CAA165 79:27:00
CAA148 65:53:00
CAA001 31:07:00
CAA170 19:13:00
CAA131 16:29:00
CAA142 14:56:00
CAA183 14:37:00
CAA156 12:06:00
CAA161 115:06:00
CAA071 10:50:00

Order By
I have a "news" database and I only want to get out the last post, not the whole database.

idnews
headline
date
content

I use the following: $sql = "Select * from news order by idnews desc";

Anyone know a command to only get the last post?

Order By
I am trying to figure out in the easiest manner.

Have a list of values
S00500
123456
156789
205000
S00067

and using this string, how do I modify the following:

$qstr .= " order by $order_by";

so it will sort to the following (number first, then letters ordered):

123456
156789
205000
S00067
S00500

Sql Order By
I have just started re-writing my forum, which is going to take a long time. While I am doing this I have a bug to solve on version 1.3, which is to do with the SQL order by.

Each post has a number, 1, 2, ect. Every time a post is added the post number increases.

When I get to 10, the ORDER BY section of my script ( I assume ) uses the first number before any others. So 10 is considered less than 2. Ruining the whole order by system.
Is there a way that this can be solved???

Order By
I have a colum that has three possible numbers

0
1
2

How would I order these records with

1 being first
2 being the second
0 being the last

ORDER BY
I have table X with UNSIGNED INT column C. I want to order by C ASC,
but have the 0 value last. So, I thought that this should work:

SELECT *, (C-1) AS o from X ORDER BY o;

but it doesn't. Doing a SELECT on (C-1) shows an extremely large
POSITIVE integer value where C=0, as I expected from an UNSIGNED column.
But it is ordered first (as -1). Is this intended behavior? Am I
doing something wrong? I worked around it for now with:

....ORDER BY C>0 DESC, C ASC;

but my curiosity is aroused... And which is faster, anyway?

Order By
in the mysql query, how do u have it order the results from newest to oldest instead of alphabetically?

Order By
I've been getting problems with the way data is extracted from a table. The table contains a column called 'name' which is a varchar(255). Each row contains strings of text... Code:

ORDER BY
This is my current MySQL query:

$query = "SELECT p.post, p.topic_id, p.pid, t.tid, t.title, t.posts, t.start_date, t.starter_name, t.forum_id, t.topic_hasattach, t.rating, t.total_votes, a.attach_id, a.attach_is_image, a.attach_hits, a.attach_pid, a2.attach_thumb_location, a2.attach_pid, r.member, r.file

FROM gf_posts p
LEFT JOIN gf_topics t ON ( p.topic_id=t.tid )
LEFT JOIN gf_attachments a ON ( p.pid=a.attach_pid )
LEFT JOIN gf_attachments a2 ON (a.attach_pid=a2.attach_pid )
LEFT JOIN gf_rated r ON ( t.tid=r.file AND $memnum=r.member )
WHERE t.forum_id=80 AND t.topic_hasattach=2 AND a.attach_is_image=0 AND a2.attach_thumb_location!=''
ORDER BY ".$orderby.", a.attach_hits desc
LIMIT $page, $limit";

Which works perfectly fine. However, I was wondering if I could have it so rows whose t.start_date>$yesterday would always display before those rows whose start_dates are earlier? I don't want to order by start_date, I just want those rows who were made in the last day always go to the top of the list, with the rows who don't fit that criteria being sorted by their rating.

I read into it a little, and it appears I can do this by GROUP BY. I tried GROUP BY(t.startdate>$yesterday) but it didn't work, so obviously I don't quite grasp the usage yet.

I also did this earlier by using two separate queries. However, this fudged up my scripting when I tried to spread the results over multiple pages.

Order By
I need to be able to order results based on several criteria.

I need to be able select all records where field:group = 'value given'
and display results sorted by these criteria:

1) if field:company is not empty then display field:company asc

2) if field:company is empty then display field:fullname asc if not empty

3) if field:company and field:fullname are empty then display field:lastname asc if not empty

4)if field:company and field:fullname and field:lastname are empty display field:firstname if not empty

these results would be easy if I could just sort all by say company but if some fields in the group have an empty company field then it doesn't get included how can I sort by all these criteria. how could I get all the records in group sorted if some fields will be empty and others not.

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 ?

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