[MySQL] groupの内部でorder byやlimitを掛けたい

Question

MySQLです。
グループごとにorder by、limitを掛けたレコードを取得したいのですが、いまいち方法が分かりません。
レコードの中でgroup by して、各グループごとにある値が最大値であるレコードだけを持ってきたテーブルが作成したいです。

例：商品マスタに以下のようなテーブルがあった時、
種類　　　番号　　　名前
フルーツ　　１　　　りんご
フルーツ　　２　　　バナナ
フルーツ　　３　　　みかん
お菓子　　　１　　　チョコ
お菓子　　　２　　　せんべい

欲しいデータ：
種類　　　番号　　　名前
フルーツ　　３　　　みかん
お菓子　　　２　　　せんべい

ある値が最大値であるものを取得する方法として、
order by と limit 1 の合わせ技を使用することで実現可能というところまでは納得したのですが、
これとgroup by が思うように組み合わせられません。
group by はorder by よりも先に処理されるとのことだったので、
順番的にはグループ化→並び替えでできるのかなと思ったのですが…
グループごとにorder by , limitをかけるというのはで実現可能でしょうか？

さらに言うならば、上記に種類マスタのようなものがありまして、種類マスタに対してそれぞれ番号がMAXである商品マスタのレコードを結合したい、というのが最終目的なのですが、
もしよい方法ご存知でしたら教えて頂きたいです。

Comments

[m.ts10806]

ご自身で試したこと調べたことをご提示ください

Answer 1

相関副問合せで、同じ種類で、もっと大きな番号が存在していないものを抽出します。

select * from 商品マスタ t
where not exists(
        select 1 from 商品マスタ where 種類 = t.種類 and 番号 > t.番号
      )

Answer 2

tacsheaven さんのデータを借りました。

SELECT	T1.*
FROM	item_master T1
WHERE	T1.sequence	= (
							SELECT	MAX( T2.sequence )
                            FROM	item_master T2
                            WHERE	T2.type	= T1.type
						)
;

Answer 3

そういうのでしたら副問合せを使って、

CREATE TABLE `item_master` (
  `type` varchar(20) NOT NULL,
  `sequence` decimal(10,0) NOT NULL,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`type`,`sequence`)
);
INSERT INTO test.item_master(type,`sequence`,name) VALUES ('お菓子',1,'チョコ');
INSERT INTO test.item_master(type,`sequence`,name) VALUES ('お菓子',2,'せんべい');
INSERT INTO test.item_master(type,`sequence`,name) VALUES ('フルーツ',1,'りんご');
INSERT INTO test.item_master(type,`sequence`,name) VALUES ('フルーツ',2,'バナナ');
INSERT INTO test.item_master(type,`sequence`,name) VALUES ('フルーツ',3,'みかん');

SELECT
  t1.type,
  t1.sequence,
  t1.name
FROM
  item_master t1 INNER JOIN
(SELECT
  type,
  MAX(sequence) as sequence
FROM item_master
GROUP BY type
) t2
ON (t1.type = t2.type AND t1.sequence = t2.sequence);

結果：

type	sequence	name
お菓子	2	せんべい
フルーツ	3	みかん

でできることはできますが。