MySQLで指定カラムに最大値を持つレコードを取得

Question

###実現したいこと
thread_ID = 11であるコードのうち、order_numberが最大のレコードを取得したいのですが、適切なSQLを教えていただけませんでしょうか。

###テーブル構成

CREATE TABLE p_comments
    (`ID` int, `thread_ID` int, `order_number` int)
;
    
INSERT INTO p_comments
    (`ID`, `thread_ID`, `order_number`)
VALUES
    (100, 11, 1),
    (101, 12, 1),
    (102, 13, 1),
    (103, 11, 2),
    (104, 13, 2),
    (105, 12, 2),
    (106, 11, 3),
    (107, 13, 3),
    (108, 11, 4)
;

###求める結果
thread_ID = 11であるレコードのうち、order_numberが最大のレコードはつまりID = 108のレコードなので以下の結果がほしいです。

尚、最大値は１つしかありません。

ID	thread_ID	order_number
108	11	4

書いてみたSQL

まずWHERE = 11と指定し、それらの最大という意図でMAX(order_number)と試したのですが、MAXの使い方が不適切なようでした。

SELECT ID, MAX(order_number) AS max_order_number
FROM p_comments
WHERE thread_ID = 11

MAXがよくわからないため、NOT EXISTSを以下のように使ってみたのですがこれでも実現できませんでした。

SELECT *
FROM p_comments AS comments1
WHERE thread_ID = 11
AND NOT EXISTS (
    SELECT 1
    FROM p_comments AS comments2
    AND comments1.order_number < comments2.order_number
);

初歩的な質問で恐縮ですが、ご回答宜しくお願い致します。

Answer 1

SELECT `ID`,`thread_ID`,`order_number` FROM `p_comments` WHERE `order_number`=(SELECT MAX(`order_number`) FROM `p_comments`);

もしくは

SELECT `ID`,`thread_ID`,`order_number`  FROM `p_comments` ORDER BY `order_number` DESC LIMIT 1;

---

MAXがよくわからないため

MySQL Maxで検索すると最初に公式リファレンス
3.6.2 特定のカラムの最大値が格納されている行
が出てくるので、公式リファレンスのサンプルを実行しながら挙動を理解されることをおすすめします。
(MySQLに限らず、公式リファレンスから情報を得ることは開発や調査においてとても重要です)

Comments

[ikatako]

もしくはの方はできませんでしたが、最初の方で実現できました。order_numberでサブクエリを書くのですね。勉強になりました。どうもありがとうございます。たしかにリファレンスにまんま載っていましたね…すみません。

[tanat]

ORDER BYの方はDESCが抜けていましたね。(修正しました)
こちらもリファレンスにあるサンプルほぼそのままなので試してみてください。

[ikatako]

１つご質問よろしいでしょうか。
目的は、特定スレッドへのコメントについて、最大のorder_numberが欲しい。というものでした。
つまりthread_ID=11を指定して4だけが欲しかったわけでIDが不要でしたので、その場合サブクエリを発行せず以下だといかがでしょうか？

```
SELECT MAX(order_number) AS max_order_number
FROM p_comments
WHERE thread_ID = 11
ORDER BY order_number LIMIT 1
```

[ikatako]

ご修正ありがとうございます。なるほど、ORDER BY に DESC を指定すれば最大が得られますね。MAXとか複雑に考えすぎていいました。
そちらの方法で進めさせて頂こうと思います。助かりました。

[tanat]

MAXを使うなら、ORDERは必要ないので
SELECT MAX(order_number) AS max_order_number
FROM p_comments
WHERE thread_ID = 11
だけでいいですね。

どちらの場合でも、現状のテーブル構造だとインデックスが効かないので少しレコード数が多くなってくると重くなるで注意が必要です。
その辺りは別途DB設計とインデックス、実行計画について調べてみると良いかと思います

[ikatako]

ちょうどインデックス勉強中でした。アドバイスありがとうございます。EXPLAINなども試して良い結果が出せるよう頑張ります。

Answer 2

select max()で指定するカラムは、group by でも指定する必要があります。
group by order_number　も追加してみてください。

もしくは、

尚、最大値は１つしかありません。
select対象のレコードが1行だけ、の認識でよければ、
「order by」+「limit 1」でもいけると思います。

Comments

[ikatako]

このコメントは編集中です

[wwwww]

select max ~ group by には、limitは必要ないです。
どちらでも可と思います！

[ikatako]

失礼いたしました。上のコメントは間違いがございました。改めて以下のように返信させて頂きます。

[ikatako]

> group by order_number　も追加してみてください。
こちらですと４つのレコードが取得されてしまいました↓
```
SELECT ID, MAX(order_number) AS max_order_number
FROM p_comments
WHERE thread_ID = 11
GROUP BY order_number
```

>「order by」+「limit 1」でもいけると思います。
order_number は4が取得できましたが、IDが108になりませんでした↓
（でもよく考えたらorder_numberだけあればいいのでこの方法でも良いかもです）
```
SELECT ID, MAX(order_number) AS max_order_number
FROM p_comments
WHERE thread_ID = 11
ORDER BY order_number LIMIT 1
```

[wwwww]

＞こちらですと４つのレコードが取得されてしまいました↓
失礼しました…。
max値を入れ子にした書き方(…tanatさんの記載のSQLの1つめのような思想)
をしないと1行にはならないですね。

[ikatako]

いえどうぞお気になさらず。むしろいろいろ試せて勉強になりました。ありがとうございます。