3.5.2 The Row Holding the Maximum of a Certain Column

``Find number, dealer, and price of the most expensive article.''

In SQL-99 (and MySQL Version 4.1) this is easily done with a subquery:

SELECT article, dealer, price
FROM   shop
WHERE  price=(SELECT MAX(price) FROM shop)

In MySQL versions prior to 4.1, just do it in two steps:

1. Get the maximum price value from the table with a SELECT statement.
2. Using this value compile the actual query:

   SELECT article, dealer, price
   FROM   shop
   WHERE  price=19.95
   

Another solution is to sort all rows descending by price and only get the first row using the MySQL-specific LIMIT clause:

SELECT article, dealer, price
FROM   shop
ORDER BY price DESC
LIMIT 1

NOTE: If there are several most expensive articles (for example, each 19.95) the LIMIT solution shows only one of them!

User Comments

Note that subqueries are not supported in versions of MySQL earlier than 4.1.

Another way I've found of doing this in one query is to get the maximum value of a concatenation starting with the column in question (in the case above, price):

SELECT MAX(CONCAT_WS('\n', price, article, dealer)) AS most_expensive FROM shop

Then you can split up the result in your language of choice (for example, in PHP):

list ($most_expensive) = mysql_fetch_assoc($query);
list ($price, $article, $dealer) = explode("\n", $most_expensive);

This method also has the advantage over the "LIMIT" method in the article because you can still do something like this:

SELECT MAX(CONCAT_WS('\n', price, article, dealer)) AS most_expensive FROM shop GROUP BY dealer

...to get the most expensive article from each dealer.


Edit: It seems this idea is not news, and a better solution has been found. See http://www.mysql.com/doc/en/example-Maximum-column-group-row.html
Sorry about that.

Problem: To find out which team scored the most points in a given week for my Fantasy Football League.

Background Info: My game Schedule table had five columns (awayTeam, awayPoints, homeTeam, homePoints, & week). So, I decided to make a temporary table to make the team IDs and points indifferent.
CREATE TEMPORARY TABLE tmp (team INT NOT NULL,
points INT,
PRIMARY KEY (team));

Populate table:
INSERT INTO tmp
SELECT awayTeam, awayPoints
FROM Schedule
WHERE week=1;
(Rinse & repeat for home team).

Here's where everything gets *stupid* (for lack of a better term). You'd expect to just do a nested select to figure the team with the most points. For example:
SELECT t.team, f.name
FROM tmp t, fflTeam f
WHERE t.team = f. id AND
t.points = (SELECT MAX(points) FROM tmp);
But this gave me an error saying, "ERROR 1137: Can't reopen table: 't' Why? I dunno. It should work, but doesn't.

Solution: My workaround was to define a variable @MostPoints and do a similar query, but not nested.
SELECT @MostPoints:=MAX(points) FROM tmp;
SELECT f.id, f.name, t.points
FROM tmp t, fflTeam f
WHERE t.team = f. id AND
t.points = @MostPoints;

Result: I have a solution set that displays the team's id, name, and how many points they scored that week. This is better than doing a SELECT team FROM tmp ORDER BY POINTS LIMIT 1 because it doesn't consider that two teams may have both scored the same amount of points.

Add your own comment.