Table of Contents for

MySQL in a Nutshell, 2nd Edition

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 client profile is viewed through the web interface, we want only the latest profile to appear. Retrieving the latest row becomes a bit cumbersome, but we can do this with MAX() and a subquery as follows:

SELECT client_name, profile, 
MAX(entry_date) AS last_entry
FROM
  (SELECT client_id, entry_date, profile 
   FROM client_profiles
   ORDER BY client_id, entry_date DESC) AS profiles
JOIN clients USING(client_id)
GROUP BY client_id;

In the subquery, we retrieve a list of profiles with the date each has in its entry in the table client_profiles; the results contain the duplicate entries for clients. In the main query, using MAX(), we get the maximum (latest) date for each client. The associated profile is included in the columns selected by the main query. We join the results of the subquery to the clients table to extract the client’s name.

The subquery is necessary so that we get the latest date instead of the oldest. The problem is that the GROUP BY clause orders the fields based on the given column. Without the subquery, the GROUP BY clause would use the value for the entry_date of the first row it finds, which will be the earliest date, not the latest. So we order the data in the subquery with the latest entry for each client first. GROUP BY then takes the first entry of the subquery results, which will be the latest entry.