Erratum for GROUP BY section (page 61)

[Conrad]

In the first print run of the book, the description of GROUP BY on page 61 is potentially misleading as the ordering of results described may not be repeatable for other data.

To fix this, in the second print run we replaced the section that starts with "If we wanted to ..." and ends with "... the cost of that kit." (just before the "JOIN" heading) with the following:

Quote:However, if we wanted to know the maximum kit cost from each manufacturer, this method could require a lot of manual inspection of the table. Instead, we would use:

SELECT manufacturer, MAX(kit_cost)
FROM Kit
GROUP BY manufacturer;

This will return a two-column table; the first column containing the name of each manufacturer and the second the cost of their most expensive kit.

Unfortunately we cannot simply get the name of the most expensive kit from that manufacturer by entering the following:

SELECT manufacturer, name, MAX(kit_cost)
FROM Kit
GROUP BY manufacturer;

expecting that it will return a three-column table; the first column containing the manufacturer, the second the name of their most expensive kit and the third the cost of that kit. This does not necessarily return the correct result as the kit name retrieved and shown is not logically linked to the result of the MAX(kit_cost) operation.

To ensure that we do get the name of the most expensive kit we can first create a temporary table ordered by kit_cost and then apply the above selection to this temporary table:

CREATE TEMPORARY TABLE Ordered_kit
SELECT manufacturer, name, kit_cost
FROM Kit
ORDER BY kit_cost DESC;

SELECT manufacturer, name, MAX(kit_cost)
FROM Ordered_kit
GROUP BY manufacturer;

As this is a temporary table, it will disappear when the database session is ended.