MySQL 5.6 リファレンスマニュアル  /  ...  /  特定のカラムの最大値が格納されている行

3.6.2 特定のカラムの最大値が格納されている行

タスク: もっとも高価な物品の品番、業者、および価格を調べます。


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

| article | dealer | price |
|    0004 | D      | 19.95 |

ほかにも、LEFT JOIN を使用する方法や、すべての行を価格の降順でソートしてから MySQL 固有の LIMIT 句を使用して最初の行だけを取得する方法もあります。

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

最高価格のものが複数あり、価格が 19.95 の場合、LIMIT を使用した方法では、その中の 1 つしか取得できません。

Download this Manual
EPUB - 7.5Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb
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.