MySQL Aggregate Functions - MAX()

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

Posted in: MySQL