とある日

select max(message) , max(create_at) from historys where history_id = 5;

このSQLの意図は、history_id が5のユーザのcreate_atが最大のmessageの内容がほしいというもの。

はじめは、問題なく動いていた。

ある時から、同じ結果しか返ってこなくなった。

なぜ、message, create_atどちらともmaxにしたか

Column順序入れ替えで結果が変わるのか。

テーブル定義

CREATE table mt (
    o int,
    t int
);

今回したいのは、一つのカラムが最大値を保持し、もう一つはなんでもいいので、簡素なテーブルを作成。

データ生成

SELECT 
    TRUNCATE(@seq_no := 1,0), 
    TRUNCATE(@seq_no1 := 1000000,0) 
UNION 
SELECT 
    TRUNCATE(@seq_no := @seq_no + 1,0),
    TRUNCATE(@seq_no1 := @seq_no1 - 1,0)  
FROM 
    information_schema.`COLUMNS` 
;

好きな数のデータを挿入してください。

select count(*) from mt;
count(*)|
--------|
   16385|

ちなみに、information_schema.COLUMNS　のテーブル件数は、

select count(*) from information_schema.`COLUMNS` c ;

count(*)|
--------|
    3390|

問題のMAX関数

やりたいこと、t列が最大のものを取得したい。

自分がやったSQL↓

select max(o),max(t) from mt;

max(o)|max(t) |
------|-------|
 16385|1000000|

取得したい結果

o|t      |
-|-------|
1|1000000|

結果が違う。

MAX関数はグループごとの最大値を返すため今回したいこととは違う。

解決方法

select o,t from mt where t = (select max(t) from mt);
o|t      |
-|-------|
1|1000000|

行ってるSQLはすごく単純。

やらなかった理由は、サブクエリをしたくなかったから。ただそれだけ。

おまけINDEX

show index from mt;

Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
-----|----------|--------|------------|-----------|---------|-----------|--------|------|----|----------|-------|-------------|-------|----------|

explain select o,t from mt where t = (select max(t) from mt);

id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows |filtered|Extra      |
--|-----------|-----|----------|----|-------------|---|-------|---|-----|--------|-----------|
 1|PRIMARY    |mt   |          |ALL |             |   |       |   |16339|    10.0|Using where|
 2|SUBQUERY   |mt   |          |ALL |             |   |       |   |16339|   100.0|           |

INDEX追加。

create index t_index on mt(t);

show index from mt;

Table|Non_unique|Key_name|Seq_in_index|Column_name|Collation|Cardinality|Sub_part|Packed|Null|Index_type|Comment|Index_comment|Visible|Expression|
-----|----------|--------|------------|-----------|---------|-----------|--------|------|----|----------|-------|-------------|-------|----------|
mt   |         1|t_index |           1|t          |A        |      16339|        |      |YES |BTREE     |       |             |YES    |          |

explain select o,t from mt where t = (select max(t) from mt);

id|select_type|table|partitions|type|possible_keys|key    |key_len|ref  |rows|filtered|Extra                       |
--|-----------|-----|----------|----|-------------|-------|-------|-----|----|--------|----------------------------|
 1|PRIMARY    |mt   |          |ref |t_index      |t_index|5      |const|   1|   100.0|Using where                 |
 2|SUBQUERY   |     |          |    |             |       |       |     |    |        |Select tables optimized away|

〆

サブクエリを書く手間を、惜しんだ悲劇であった。

今後このようなことがないように、楽にSQLを書くことをしないで起きたい。

今日はなにの日。

気になったこと勉強になったことのメモ。

今日は、MAX関数2つでやらかしてた話の日。

目次