Documentation Home
MySQL 5.6 リファレンスマニュアル
Download this Manual
PDF (US Ltr) - 26.8Mb
PDF (A4) - 26.9Mb
HTML Download (TGZ) - 7.1Mb
HTML Download (Zip) - 7.2Mb


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.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;
注記

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


User Comments
User comments in this section are, as the name implies, provided by MySQL users. The MySQL documentation team is not responsible for, nor do they endorse, any of the information provided here.
  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,
MAX(@article),
MAX(@dealer),
MAX(@price)
FROM
shop,
(SELECT @article:=0) r ,
(SELECT @dealer:=0) r2,
(SELECT @price:=0) r3
GROUP BY
@price:=-10,
@article:='',
@dealer:='';

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:

SELECT
t1.id_client,
t1.or_res,
t2.`comment`,
call_reason
FROM
(SELECT
MAX(call_back_hour) as or_res,
id_client
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.