Randomly ordering a MySQL result set

There are times when you might need to randomly order a MySQL resultset, for example when choosing some winners at random for a prize draw. This post looks at how you would do this and the next MySQL post will look at "randomly" ordering data across multiple pages.

Using the prize draw example, let's say we conducted a survey and were going to give a prize to 10 of the people who completed the survey. These people are to be chosen at random. The table is called "survey" and we want their contact details.

To select 10 records from the table in random order, do the following:

SELECT first_name, last_name, email_address, phone
FROM survey
ORDER BY RAND()
LIMIT 10

The ORDER BY RAND() line is what makes the resultset random, and the LIMIT 10 line returns just the first 10 results.

Ordering a resultset randomly in this way is inefficient and will be slow on large resultsets, because the random number has to be applied to every row before it's sorted. For the purposes of choosing a winner or similar sorts of circumstances it's great but it's not that useful if you need to page "random" results over several pages.

If you do need to page data "randomly" using RAND() is not much use because the same records will appear on multiple pages, and you won't get a consistent data set as you page back and forward through the same pages. I will look at how to do this in next week's MySQL post. Make sure to subscribe to my RSS feed (details below) so you don't miss out.

Related posts:

• An alternative to ORDER BY RAND() for MySQL
• Rounding numbers with MySQL
• "Randomly" order data across multiple pages with MySQL
• Using "group by" and "having" with MySQL
• Find the length of the longest string in MySQL

MySql does not return results as expected

delete FROM tuan_details where tuan_id<>14

This should keep only those rows where tuan_id equals 14, but the rows where tuan_id is null are also being preserved.

Why is it so?

---

NULL is special in SQL. The condition you have: tuan_id <> 14 will be TRUE only for values that are different than 14. For rows that tuan_id is NULL, the:

tuan_id <> 14

will be:

NULL <> 14

which evaluates to:

UNKNOWN

so these rows are not deleted. WHERE conditions are satisfied when they evaluate to TRUE. They are rejected when they evaluate to FALSE or UNKNOWN.

SQL uses a 3-valued logic

---

So, if you want to delete Nulls as well, you can use this statement:

DELETE
FROM tuan_details
WHERE tuan_id <> 14
   OR tuan_id IS NULL ;

The left join of Mysql fails to return all values ​​from the first table

I've two tables named table1 and table2. table2 may have elements from table1. I'd like to show all the results from table2 with the price if available in table1 and with status 1. If no product matches in table1 and also status is 0, need to return price as 0.

table1

id  pname       item_code   price   status

1   product1    abcd        200       1
2   product2    pqrs        500       1
3   product3    wxyz        425       1
4   product5    mnop        100       0

and table2 as follows

id  item_code   

10  efgh
11  abcd
12  pqrs
13  mnop

I have tried following query

SELECT `t2`.`id`, `t2`.`item_code`, `t1`.`price`,`t1`.`pname`, COUNT(t2.item_code) AS sellers FROM (`table2` as t2) LEFT JOIN `table1` as t1 ON `t1`.`item_code` = `t2`.`item_code` WHERE `t1`.`status` = 1 GROUP BY `t2`.`item_code`

but it returns common values in table1 and table2 with status 1, but I need all records from table2 with price as 0 if nothing match in table1 or status 0 in table1.

Expected output

id  item_code   price

10  efgh        0
11  abcd        200
12  pqrs        500
13  mnop        0

Any help please.

Thanks,

---

Not sure about your current query which is having count and group by however you can do as below

select
t2.id,
t2.item_code,
case
  when t1.status = 1 then t1.price
  else 0
end as price
from table2 t2
left join table1 t1 on t1.item_code = t2.item_code

Now note that if table1 has multiple matching values from table2 in that case we may need grouping data.

Mysql does not return the results, otherwise the statement, but the INDEX table or something

I think my question was a little confusing.....It confused me :)

Working on a media site as a take-over project and it has a custom CMS. The client wants the ability to activate/deactivate media....sort of like Wordpress's publish/unpublish feature.

Instead of digging through all the code looking for mysql queries (which I'm not opposed to), I was wondering if you can add a sort of INDEX to a table that won't let it return result rows if that rows "active" column = let's say 0.

Just trying to be lazy and learn something at the same time, heh.

I don't need examples of queries to make it happen, btw.

---

What you describe is called a "view". Here is a page describing how to create them in MySQL: http://dev.mysql.com/doc/refman/5.0/en/create-view.html. However, in most cases you will still have to alter your code to use the view instead of the table.

MySQL does not return all results from my InnoDB table?

I am trying to get hold of 1 record from a MySQL table using PHP. I have tried many different SELECT statements and had no luck so decided to ask PHP to show me ALL results for this certain column. It returned all results EXCEPT the first result.

Im guessing this is why when it finds the result i need from the SELECT statement it does find a value but for some reason doesn't give it to me?

Its probably really obvious but i accept defeat now, please help!

$query="SELECT cw_id FROM unihubUpcoming";
$result = mysql_query($query) or die(mysql_error());

if(!$result){
 die('Query Failed!');
}

$row = mysql_fetch_assoc($result);

while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
 echo $row[0];
}

All that code does is execute the $query and print all items out but the first result found.

Thank you guys!

---

// get the first result
$row = mysql_fetch_assoc($result);
// but don't do anything with it

// loop and display all subsequent results
while ($row = mysql_fetch_array($result,MYSQL_NUM)) {
 echo $row[0];
}




MySQL Subquery does not return the expected results

I have a MySQL table with communities and their corresponding MLS map area. Some communities are large and take up multiple map areas.

I am attempting to do a query that returns the most common communities and their map area when passed multiple map areas.

The query I am trying for map areas 601 and 606 is:

SELECT DISTINCT(community), mapArea FROM (
    SELECT community, mapArea
    FROM single_family
    GROUP BY community
    ORDER BY COUNT(community) DESC) AS query1
WHERE mapArea LIKE '601%' OR mapArea LIKE '606%' ORDER BY community

Example single_family Table Layout (actual table has over 60k rows):

community    mapArea
Solera       606 - Henderson
Solera       606 - Henderson
Solera       204 - East
Solera       606 - Henderson
Solera       202 - East
Anthem       606 - Henderson
Green Valley 601 - Henderson
Green Valley 601 - Henderson
Green Valley 606 - Henderson
Seven Hills  606 - Henderson
Seven Hills  606 - Henderson

If I run a count on the table it shows:

community      mapArea           countCommunity
Anthem         606 - Henderson   776
Solera         606 - Henderson   58
Solera         204 - East        6
Solera         202 - East        1
Green Valley   601 - Henderson   188
Green Valley   606 - Henderson   117
Seven Hills    606 - Henderson   372

When I run the above query for map areas 601 and 606 I get the following which is correct for some communites but the community of Solera for example is not listed:

community     mapArea
Anthem        606 - Henderson
Green Valley  601 - Henderson
Seven Hills   606 - Henderson

Since Solera has the most rows with the map area being 606 - Henderson, I am wondering what is wrong in the query to why it is not being included.

Any help to why this is not returning the expected results and what I have to do to get the expected results is very much appreciated.

---

You are grouping by community, which in result, might hide mapArea lines within the grouped lines. try this instead:

SELECT community, mapArea FROM (
    SELECT community, mapArea
    FROM single_family
    WHERE mapArea LIKE '601%' OR mapArea LIKE '606%'
    GROUP BY community
    ORDER BY COUNT(community) DESC) AS query1
ORDER BY community

also, why using DISTINCT when you're really grouping by the same column ? the subquery will result in rows with distinct values of communities. using DISTINCT will just slow down the process

The mysql declaration does not return the correct result

I have a mysql row with 'date = 2013-05-02, type = 1' etc.

Then I run this query

SELECT date, type, status, rate
FROM reservation
WHERE  type = 1
AND date BETWEEN 2013-05-01 AND 2013-05-08
ORDER BY date asc
LIMIT 0, 10

But this returns empty results. What is the query issue here?

---

Put the dates in quotes ' in the mysql query.

SELECT date, type, status, rate
FROM reservation
WHERE  type = 1
AND date BETWEEN '2013-05-01' AND '2013-05-08'
ORDER BY date ASC
LIMIT 0, 10 ;