SQL - About MAX(...)

/Home

I'm currently teaching database & SQL to a couple of students and I think I'll blog a little bit about the caveats of SQL based on that experience.

SELECT MAX(`VALUE`) FROM `TABLE`;

It's fairly simple to find the maximum by using the MAX agregation function but we don't need the MAX function (but of course: you should use it). In other terms: Maximum means that we can not find a value that is smaller so we can express this using NOT EXISTS:

SELECT `VALUE` FROM `TABLE` `A` 
WHERE NOT EXISTS (
  SELECT * FROM `TABLE` `B`
  WHERE `B`.`VALUE` > `A`.`VALUE`);

Let's say we have a table with ages and names:

CREATE TABLE `TABLE` (
  `AGE` INT NOT NULL, 
  `NAME` VARCHAR(255) NOT NULL
);

We can easily find out the maximum age but finding the name of the person with the highest age is a bit more tricky. Here's why:

mysql> SELECT * FROM `TABLE`;
+-----+------+
| AGE | NAME |
+-----+------+
|   5 | John |
|  10 | Jane |
|   7 | Fred |
+-----+------+
3 rows in set (0.00 sec)

mysql> SELECT MAX(`AGE`), `NAME` FROM `TABLE`;
+------------+------+
| MAX(`AGE`) | NAME |
+------------+------+
|         10 | John |
+------------+------+

We can't just do SELECT MAX(`AGE`), `NAME`. The age will be correct, but `NAME` is just the first name in our table. Instead, we should be doing something like this:

mysql> SELECT `AGE`, `NAME` FROM `TABLE` 
       WHERE `AGE` = (SELECT MAX(`AGE`) FROM `TABLE`);
+-----+------+
| AGE | NAME |
+-----+------+
|  10 | Jane |
+-----+------+

Another example. We have products and vendors that sell these products at a specific price:

mysql> SELECT * FROM `PRODUCTS`;
+--------+-------+---------+
| NAME   | PRICE | VENDOR  |
+--------+-------+---------+
| Banana |   0.5 | Walmart |
| Banana |  0.75 | Lidl    |
| Copper |  5.55 | Manor   |
| Copper |  4.44 | Denner  |
+--------+-------+---------+

We want to know what the cheapest price for a product is and where we can buy that product for that price. The first part only is rather trivial with a GROUP BY:

mysql> SELECT `NAME`, MIN(`PRICE`) 
       FROM `PRODUCTS` GROUP BY `NAME`;
+--------+--------------+
| NAME   | MIN(`PRICE`) |
+--------+--------------+
| Banana |          0.5 |
| Copper |         4.44 |
+--------+--------------+

But how do we get the vendor? We select all rows where the price is equal to the minimum price for that product. I can come up with two ways of doing this. One using two selects:

mysql> SELECT `NAME`, `PRICE`, 
              `VENDOR` 
       FROM `PRODUCTS` `P1` 
       WHERE `P1`.`PRICE` = (
        SELECT MIN(`PRICE`) 
        FROM `PRODUCTS` `P2` 
        WHERE `P2`.`NAME` = `P1`.`NAME`);
+--------+-------+---------+
| NAME   | PRICE | VENDOR  |
+--------+-------+---------+
| Banana |   0.5 | Walmart |
| Copper |  4.44 | Denner  |
+--------+-------+---------+

The above one is a bit more intuitive but we can also do it with a join:

mysql> SELECT `P1`.`NAME`, `N` AS `PRICE`, 
              `P1`.VENDOR
       FROM `PRODUCTS` `P1` 
       JOIN ( 
        SELECT `NAME`, MIN(`PRICE`) AS `N` 
        FROM `PRODUCTS` `P2` 
        GROUP BY `NAME`) `P2` 
       ON `P1`.`NAME` = `P2`.`NAME`
       WHERE `P1`.`PRICE` = `N`;
+--------+-------+---------+
| NAME   | PRICE | VENDOR  |
+--------+-------+---------+
| Banana |   0.5 | Walmart |
| Copper |  4.44 | Denner  |
+--------+-------+---------+

Click here to show comments