GROUP BY後、最小・最大の値を抽出する

地味に調べたのでメモ。

こたえ

SELECT
  m1.item_id,
  m1.price,
  m1.sale_price
FROM
  items m1,
  items m2
GROUP BY
  m1.item_id, m1.price, m1.sale_price, m2.category_id
HAVING
  MIN(m2.sale_price) = m1.sale_price;

サンプルテーブル作成

CREATE TABLE `items` (
  `category_id` int(11) DEFAULT NULL,
  `item_id` int(11) DEFAULT NULL,
  `price` double DEFAULT NULL,
  `sale_price` double DEFAULT NULL
);

INSERT INTO items VALUES(1, 1, 300, 150);
INSERT INTO items VALUES(1, 2, 200, 100);
INSERT INTO items VALUES(1, 3, 150, 110);
INSERT INTO items VALUES(2, 4, 1300, 1150);
INSERT INTO items VALUES(2, 5, 1200, 1100);
INSERT INTO items VALUES(2, 6, 1150, 1110);

サンプルテーブル確認

mysql> SELECT * FROM items;
+-------------+---------+-------+------------+
| category_id | item_id | price | sale_price |
+-------------+---------+-------+------------+
|           1 |       1 |   300 |        150 |
|           1 |       2 |   200 |        100 |
|           1 |       3 |   150 |        110 |
|           2 |       4 |  1300 |       1150 |
|           2 |       5 |  1200 |       1100 |
|           2 |       6 |  1150 |       1110 |
+-------------+---------+-------+------------+
6 rows in set (0.00 sec)

実行

mysql> SELECT
    ->   m1.item_id,
    ->   m1.price,
    ->   m1.sale_price
    -> FROM
    ->   items m1,
    ->   items m2
    -> GROUP BY
    ->   m1.item_id, m1.price, m1.sale_price, m2.category_id
    -> HAVING
    ->   MIN(m2.sale_price) = m1.sale_price;
+---------+-------+------------+
| item_id | price | sale_price |
+---------+-------+------------+
|       2 |   200 |        100 |
|       5 |  1200 |       1100 |
+---------+-------+------------+
2 rows in set (0.00 sec)

追記

うーん、、、なんか違う気がしたのでプログラム側で集計するようにした。MySQLにもRANK関数があればいいのに。