MySQL Aggregate Functions - MAX()
This lesson will teach you how to use the MAX() aggregate function . If you missed
the Aggregate Introduction Lesson, please check it out now, as it explains many concepts used in this lesson!
You can download the example the products.sql file from our website, which contains the SQL for the table below.
If you would like to use PHP/MySQL to create the table, then you will need to know how to Create a MySQL Table and Insert a MySQL Row.
Below is the MySQL table "products".
Products Table:
| id | name | type | price |
| 123451 | Park's Great Hits | Music | 19.99 |
| 123452 | Silly Puddy | Toy | 3.99 |
| 123453 | Playstation | Toy | 89.95 |
| 123454 | Men's T-Shirt | Clothing | 32.50 |
| 123455 | Blouse | Clothing | 34.97 |
| 123456 | Electronica 2002 | Music | 3.99 |
| 123457 | Country Tunes | Music | 21.55 |
| 123458 | Watermelon | Food | 8.73 |
MySQL MAX - Finding the Big One
MySQL's MAX aggregate function will find
the largest value in a group. The "products" table that is displayed above has several products of various types. We could use the MAX function to find the most expensive item for each type of product.
Just as we did in the aggregate introduction lesson, we are going to GROUP BY type to create four groups: Music, Toy, Clothing and Food. We will also be applying the aggregate function to the price column.
PHP and MySQL Code:
<?php
// Make a MySQL Connection
$query = "SELECT type, MAX(price) FROM products GROUP BY type";
$result = mysql_query($query) or die(mysql_error());
// Print out result
while($row = mysql_fetch_array($result)){
echo "The most expensive ". $row['type']. " is $" .$row['MAX(price)'];
echo "<br />";
}
?>
Display:
The most expensive Clothing is $34.97
The most expensive Food is $8.73
The most expensive Music is $21.55
The most expensive Toy is $89.95
Download Tizag.com's MySQL Book
If you would rather download the PDF of this tutorial, check out our
MySQL eBook from the Tizag.com store.
You may also be interested in getting the PHP eBook Found Something Wrong in this Lesson?Report a Bug or Comment on This Lesson - Your input is what keeps Tizag improving with time! | 
