Using Joins and Subqueries - MySQL - page 507

Then use the dates table as shown earlier in this section to fill holes in the summary or
to detect holes in the dataset.
You can find the make_date_list() procedure in the joins directory of the recipes
distribution. It requires the exec_stmt() and quote_identifier() helper routines (see
Recipe 9.9 ), located in the routines directory. The joins directory also contains a Perl
script, make_date_list.pl , that implements an alternate approach; it generates date ref‐
erence tables from the command line.
14.9. Using a Join to Control Query Sort Order
Problem
You want to sort a statement's output using a characteristic of the output that cannot be
specified using ORDER BY . For example, you want to sort a set of rows by subgroups,
putting first those groups with the most rows and last those groups with the fewest rows.
But “number of rows in each group” is not a property of individual rows, so you can't
use it for sorting.
Solution
Derive the ordering information and store it in an auxiliary table. Then join the original
table to the auxiliary table, using the auxiliary table to control the sort order.
Discussion
Most of the time you sort a query result using an ORDER BY clause that names which
column or columns to use for sorting. But sometimes the values you want to sort by
aren't present in the rows to be sorted. This is the case when you want to use group
characteristics to order the rows. The following example uses the driver_log table to
illustrate this. The following query sorts the table using the ID column, which is present
in the rows:
mysql> SELECT * FROM driver_log ORDER BY rec_id;
+--------+-------+------------+-------+
| rec_id | name | trav_date | miles |
+--------+-------+------------+-------+
| 1 | Ben | 2014-07-30 | 152 |
| 2 | Suzi | 2014-07-29 | 391 |
| 3 | Henry | 2014-07-29 | 300 |
| 4 | Henry | 2014-07-27 | 96 |
| 5 | Ben | 2014-07-29 | 131 |
| 6 | Henry | 2014-07-26 | 115 |
| 7 | Suzi | 2014-08-02 | 502 |
| 8 | Henry | 2014-08-01 | 197 |
| 9 | Ben | 2014-08-02 | 79 |