Using a Join to Control Query Output Order
Problem
You want to sort
a query’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 sort by it.
Solution
Derive the ordering information and store it in another table. Then join the original table to the derived table, using the derived table to control the sort order.
Discussion
Most of the time when you sort a query result, you use an
ORDER
BY
(or
GROUP
BY
) clause to name the
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, for example, if you want to use group
characteristics to order the rows. The following example uses the
records in the driver_log
table to illustrate
this. The table looks like this:
mysql> SELECT * FROM driver_log ORDER BY id;
+--------+-------+------------+-------+ | rec_id | name | trav_date | miles | +--------+-------+------------+-------+ | 1 | Ben | 2001-11-30 | 152 | | 2 | Suzi | 2001-11-29 | 391 | | 3 | Henry | 2001-11-29 | 300 | | 4 | Henry | 2001-11-27 | 96 | | 5 | Ben | 2001-11-29 | 131 | | 6 | Henry | 2001-11-26 | 115 | | 7 | Suzi | 2001-12-02 | 502 | | 8 | Henry | 2001-12-01 | 197 | | 9 | Ben | 2001-12-02 | 79 | | 10 | Henry | 2001-11-30 | 203 | +--------+-------+------------+-------+ ...
Get MySQL Cookbook now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.