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