Database Reference
In-Depth Information
| Ian McKellen |
| Orlando Bloom |
+---------------+
• List the movies in which a given actor has acted:
mysql>
SELECT m.year, m.movie
->
FROM movies AS m INNER JOIN movies_actors_link AS l
->
INNER JOIN actors AS a
->
ON m.id = l.movie_id AND a.id = l.actor_id
->
WHERE a.actor = 'Liam Neeson'
->
ORDER BY m.year, m.movie;
+------+--------------------+
| year | movie |
+------+--------------------+
| 1999 | The Phantom Menace |
| 2005 | Kingdom of Heaven |
| 2011 | Unknown |
+------+--------------------+
14.7. Finding Per-Group Minimum or Maximum Values
Problem
You want to find which row within each group of rows in a table contains the maximum
or minimum value for a given column. For example, you want to determine the most
expensive painting in your collection for each artist.
Solution
Create a temporary table to hold the per-group maximum or minimum values, then
join the temporary table with the original one to pull out the matching row for each
group. If you prefer a single-query solution, use a subquery in the
FROM
clause rather
than a temporary table.
Discussion
Many questions involve finding largest or smallest values in a particular table column,
but it's also common to want to know other values in the row that contains the value.
For example, using the
artist
and
painting
tables with the techniques from
Recipe 8.3
, it's possible to answer questions such as “What is the most expensive painting
in the collection, and who painted it?” One solution is to store the highest price in a
user-defined variable, then use the variable to identify the row containing the price so
that you can retrieve other columns from it:
mysql>
SET @max_price = (SELECT MAX(price) FROM painting);
mysql>
SELECT artist.name, painting.title, painting.price
