MySQL in a Nutshell, 2nd Edition

Name

MAX()

Synopsis

MAX(expression)

This function returns the highest number in the values for a given column. It’s normally used in conjunction with a GROUP BY clause specifying a unique column, so that values are compared for each unique item separately.

As an example of this function, suppose that we wanted to know the maximum sale for each sales person for the month. We could enter the following SQL statement:

SELECT CONCAT(name_first, SPACE(1), name_last) AS rep_name, 
MAX(sale_amount) AS biggest_sale
FROM sales
JOIN sales_reps USING(sales_rep_id)
WHERE DATE_FORMAT(date_of_sale, '%Y%m') = 
      DATE_FORMAT(CURDATE(), '%Y%m')
GROUP BY sales_rep_id DESC;

We’ve given sale_amount as the column for which we want the largest value returned for each sales rep. The WHERE clause indicates that we want only sales for the current month. Notice that the GROUP BY clause includes the DESC keyword. This will order the rows in descending order for the values of the biggest_sale field: the biggest sale at the top, the smallest at the bottom.

Here’s an example of another handy but less obvious use of this function: suppose we have a table in which client profiles are kept by the sales people. When a sales rep changes a client profile through a web interface, instead of updating the existing row, the program we wrote creates a new entry. We use this method to prevent sales people from inadvertently overwriting data and to keep previous client profiles in case someone wants to refer to them later. When the ...