Skip navigation links
MySQL 3.23, 4.0, 4.1 Reference Manual :: 3 Tutorial :: 3.6 Examples of Common Queries :: 3.6.2 The Row Holding the Maximum of a Certain Column
3.6.3 Maximum of Column per Group »
Section Navigation      [Toggle]

3.6.2. The Row Holding the Maximum of a Certain Column

Task: Find the number, dealer, and price of the most expensive article.

This is easily done with a subquery: 

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

+---------+--------+-------+
| article | dealer | price |
+---------+--------+-------+
|    0004 | D      | 19.95 |
+---------+--------+-------+

Other solutions are to use a LEFT JOIN or to sort all rows descending by price and get only the first row using the MySQL-specific LIMIT clause: 

SELECT s1.article, s1.dealer, s1.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

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

Note

If there were several most expensive articles, each with a price of 19.95, the LIMIT solution would show only one of them.


User Comments

Posted by Andrew Ford on July 20 2003 5:10am[Delete] [Edit]

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.

Posted by Toks on October 15 2003 5:25am[Delete] [Edit]

How about you try this and see if it works with your first query.

SELECT t.team, f.name
FROM tmp t, fflTeam f
WHERE t.team = f. id AND
t.points = (SELECT MAX(j.points) FROM tmp j);

This will select the max points from tmp by joining tmp to itself with 2 differenmt aliases.

Posted by Phil Beynon on March 2 2004 12:56pm[Delete] [Edit]

This may be useful!

I have a table (addressmappings) where the contents of the field 'tablename' are 'address1' through to 'address65' I use these as references to other tables in a lookup system.
I need to know from this mixed alphanumeric which will be the next table name to create (they come and go).

if I do:

SELECT SUBSTRING(tablename,8) AS high FROM addressmappings;

then this returns all the numbers with the word 'address' stripped off as you would expect.
However if I do:

SELECT MAX(SUBSTRING(tablename,8)) AS high FROM addressmappings;

then this returns:-

+------+
| high |
+------+
| 9    |
+------+

Instead of the expected 65

This is because it sees the 9 in the first position as being higher than the 6 in 65.
The workaround is actually quite simple, if you multiply 65 by 100 it becomes 6500 and 9 becomes 900 - it is now bigger, but obviously now the wrong number by a magnitude.
So we divide it by 100, 6500 reverts back to 65, 900 to 9 and the correct result is displayed.

The syntax being;

SELECT MAX(SUBSTRING(tablename,8)*100)/100 AS high FROM addressmappings;

+------+
| high |
+------+
|   65 |
+------+

I have no idea if this is a bug - but this fix worked well for me, albeit after a couple of hours!

Regards,

Phil

Posted by Thang Le on November 4 2004 6:52pm[Delete] [Edit]

My take on the issue is because in the original statement, SUBSTRING() is used, so MAX() tries to make string comparison, and so "9" is larger than "65". The work around implicitly converts the result into numeric values, and so MAX() uses numerical comparison, which gives the correct result.

In this view I suspect that if you just multiply the result of SUBSTRING() with 1, it'll still works, but I haven't yet tested this out.

Posted by Thang Le on November 4 2004 7:32pm[Delete] [Edit]

I check around a little. It seems the prefered way to convert strings to numerical values (at least by MySQL staff) is to add 0 (for integers) or 0.00 (for doubles). So the SQL statement above can be written as:

SELECT MAX(SUBSTRING(tablename,8) + 0) AS high FROM addressmappings;

Posted by Balu Pugazhendhi on December 24 2004 9:53am[Delete] [Edit]

You can achieve the same using alias

SELECT a.article, a.dealer, a.price
FROM shop a, shop b
GROUP by a.article, a.dealer, a.price
HAVING MAX(b.price) = a.price


Posted by Aleksey Gorochkin on February 15 2005 8:47pm[Delete] [Edit]

The alias idea is nice but I don't think it would work with a temporary table. There is another way you can do it though (this was suggested by a co-worker):

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

Posted by arthur melkonyan on May 17 2005 8:43pm[Delete] [Edit]

It should work this way

SELECT t1.name, t1.lastname, t1.id, t2.week, t2.score FROM tablename1 t1, tablename2 t2
WHERE t1.id=t2.id group by t2.week desc limit 1;

Posted by Rick Dunnivan on November 6 2005 2:06pm[Delete] [Edit]

I had this same problem with my fantasy league...
Here is how I solved the problem.


select IF(HomeScore > AwayScore, HomeTeam, AwayTeam) as team, IF(HomeScore > AwayScore, HomeScore, AwayScore) as high from GameData where Week = 8 and Year = 2005 order by high desc limit 0,1;


My actual statement is a little more complex to find ties and whatnot, but this is the basic idea.

Add your own comment.