Database Reference
In-Depth Information
| 10 | Henry | 2014-07-30 | 203 |
+--------+-------+------------+-------+
But what if you want to display a list and sort it on the basis of a summary value not
present in the rows? That's a little trickier. Suppose that you want to show each driver's
rows by date, but place those drivers who drive the most miles first. You can't do this
with a summary query because then you wouldn't get back the individual driver rows.
But you can't do it without a summary query, either, because the summary values are
required for sorting. The way out of the dilemma is to create another table containing
the summary value per driver and join it to the original table. That way you can produce
the individual rows and also sort them by the summary values.
To summarize the driver totals into another table, do this:
mysql>
CREATE TABLE tmp
->
SELECT name, SUM(miles) AS driver_miles FROM driver_log GROUP BY name;
That produces the values we need to put the names in the proper total-miles order:
mysql>
SELECT * FROM tmp ORDER BY driver_miles DESC;
+-------+--------------+
| name | driver_miles |
+-------+--------------+
| Henry | 911 |
| Suzi | 893 |
| Ben | 362 |
+-------+--------------+
Then use the
name
values to join the summary table to the
driver_log
table, and use
the
driver_miles
values to sort the result:
mysql>
SELECT tmp.driver_miles, driver_log.*
->
FROM driver_log INNER JOIN tmp ON driver_log.name = tmp.name
->
ORDER BY tmp.driver_miles DESC, driver_log.trav_date;
+--------------+--------+-------+------------+-------+
| driver_miles | rec_id | name | trav_date | miles |
+--------------+--------+-------+------------+-------+
| 911 | 6 | Henry | 2014-07-26 | 115 |
| 911 | 4 | Henry | 2014-07-27 | 96 |
| 911 | 3 | Henry | 2014-07-29 | 300 |
| 911 | 10 | Henry | 2014-07-30 | 203 |
| 911 | 8 | Henry | 2014-08-01 | 197 |
| 893 | 2 | Suzi | 2014-07-29 | 391 |
| 893 | 7 | Suzi | 2014-08-02 | 502 |
| 362 | 5 | Ben | 2014-07-29 | 131 |
| 362 | 1 | Ben | 2014-07-30 | 152 |
| 362 | 9 | Ben | 2014-08-02 | 79 |
+--------------+--------+-------+------------+-------+
The preceding statement shows the mileage totals in the result. That's only to clarify
how the values are being sorted. It's not actually necessary to display them; they're
needed only for the
ORDER
BY
clause.
