MySQL Subqueries - a Useful Example

I can remember when MySQL hit the streets back in 1995 - it was quite a raw product but still extremely useful for some development I was engaged in on a Linux Softlanding platformed box. While MySQL grew to be more and more popular, Linux SLS withered and died due to its bugs, but was still responsible for spawning the Debian distro amongst others.

MySQL didn't have subqueries from the start. Indeed, it was version 4.1 before they first appeared. Maybe because they didn't come along until so late in the day may explain why I always have to refresh my memory on how to build a statement using the correct syntax. So this exercise is as much an aide memoire to myself as a tutorial to the general public.

So here's the example. I have a table with three integer values. The first is the primary key although for our example it isn't important.

mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> describe badzilla;
+--------+---------+------+-----+---------+-------+
| Field  | Type    | Null | Key | Default | Extra |
+--------+---------+------+-----+---------+-------+
| nid    | int(11) | NO   | PRI | NULL    |       |
| dponid | int(11) | NO   |     | NULL    |       |
| cronid | int(11) | NO   |     | NULL    |       |
+--------+---------+------+-----+---------+-------+
3 rows in set (0.00 sec)

The data is shown below, and I have edited the output to display exactly what data I need to extract, and by that fact, how we are going to construct our subquery. In words, I need the dponid where the cronid is the highest for that dponid. So the single maximum values of cronid per dponid. That definitely cannot be achieved with a 'regular' MySQL query.
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from badzilla;
+-----+--------+--------+
| nid | dponid | cronid |
+-----+--------+--------+
|  10 |    155 |      2 |<---- I want this value
|  11 |    155 |      1 |
|  12 |    150 |      1 |<---- And this one too!
|  13 |    150 |      0 |
+-----+--------+--------+
4 rows in set (0.00 sec)

The solution took a little thinking about, but the best way to solve this sort of problem is to work backwards. So firstly, I want to select the maximum values of cronid which would be SELECT MAX(cronid) FROM badzilla. Secondly, I need to add a clause that's saying select the respective dponid for that particular condition. Put the two together, add the required aliases, and the result is:
mysql> use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> select * from badzilla a where a.cronid = (select max(b.cronid) from badzilla b where b.dponid = a.dponid);
+-----+--------+--------+
| nid | dponid | cronid |
+-----+--------+--------+
|  10 |    155 |      2 |
|  12 |    150 |      1 |
+-----+--------+--------+
2 rows in set (0.04 sec)

mysql>

Now I've committed it, I can check back here next time I can't remember how to construct a subquery.