MySQL 5.0 Reference Manual  /  ...  /  The Row Holding the Maximum of a Certain Column

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.price
FROM shop s1
LEFT JOIN shop s2 ON s1.price < s2.price
WHERE s2.article IS NULL;

SELECT article, dealer, price
FROM shop

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

Download this Manual
User Comments
  Posted by Toks Adejuyigbe on October 15, 2003
How about you try this and see if it works with your first query.

FROM tmp t, fflTeam f
WHERE = 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
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!


  Posted by Thang Le on November 4, 2004
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
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
You can achieve the same using alias

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

  Posted by Aleksey Gorochkin on February 15, 2005
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

  Posted by arthur melkonyan on May 17, 2005
It should work this way

SELECT, t1.lastname,, t2.week, t2.score FROM tablename1 t1, tablename2 t2
WHERE group by t2.week desc limit 1;
  Posted by Richard Dunnivan on November 6, 2005
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.
  Posted by obed Castillo on April 27, 2010
using the same tables, if we want to know the youngest pet.

mysql> select name, owner, birth
-> from pet
-> where birth = (SELECT MAX(birth) from pet);

  Posted by eded eded on March 3, 2011
Maybe an optimized way to obtain the same results. Actually, opening the table only once :

SELECT MAX(CASE WHEN @price<=price THEN CONCAT(@price:=price,@article:=article,@dealer:=dealer) ELSE 0 END) AS dummy,
(SELECT @article:=0) r ,
(SELECT @dealer:=0) r2,
(SELECT @price:=0) r3

Hopefully this method can be faster in some cases.

  Posted by Alexandru Iancu on October 30, 2012
Let's asume we have a table with:

client_id | call_back_hour | call_reason | comment

We want to extract only the last calls rescheduled(reason 2) with their comments from the table:

Here is the method i used:

MAX(call_back_hour) as or_res,
from ai_calls
WHERE call_reason =2
GROUP BY id_client)
as t1
LEFT JOIN ai_calls as t2 on
t1.id_client=t2.id_client and t1.or_res=t2.call_back_hour
Sign Up Login You must be logged in to post a comment.