特定の条件で集約し、最小値を持つ行を取得する方法

データベースで作業する際に、特定の条件に基づいて集約し、その中で最小値を持つ行を取得したいことがあります。
例えば、特定のcategoryが設定されている場合に、その中でitem_idが最も小さい行を取得したい場合です。
今回はその方法について解説します。

例: 集約して最小のitem_idを持つ行を取得する

まず、以下のようなサンプルデータがあるとします。

CREATE TABLE sample_table (
    category VARCHAR(50),
    product_name VARCHAR(50),
    price DECIMAL(10, 2),
    supplier VARCHAR(50),
    stock INT,
    location VARCHAR(50),
    item_id VARCHAR(10)  -- item_idは文字列として扱います
);

INSERT INTO sample_table VALUES
('Electronics', 'Smartphone', 599.99, 'Supplier A', 100, 'Warehouse 1', '1'),
('Electronics', 'Smartphone', 599.99, 'Supplier A', 100, 'Warehouse 1', '2'),
('Furniture', 'Chair', 49.99, 'Supplier B', 200, 'Warehouse 2', '1'),
('Furniture', 'Chair', 49.99, 'Supplier B', 200, 'Warehouse 2', '3'),
('Appliances', 'Refrigerator', 299.99, 'Supplier C', 50, 'Warehouse 3', '-');

このデータセットから、各categoryごとにitem_idが最も小さい行を取得したいとします。
item_idが「-」の場合も考慮する必要があります。

サブクエリを使用した対応策

以下に示すSQLクエリでは、サブクエリを使用して各グループ（category、product_name、supplier、locationの組み合わせ）ごとに最小のitem_idを持つ行をフィルタリングしています。

SELECT 
    category,
    product_name,
    price,
    supplier,
    stock,
    location,
    item_id
FROM 
    sample_table t1
WHERE 
    item_id = (
        SELECT MIN(item_id)
        FROM sample_table t2
        WHERE t2.category = t1.category
          AND t2.product_name = t1.product_name
          AND t2.supplier = t1.supplier
          AND t2.location = t1.location
    )
ORDER BY
    category ASC;

このクエリのポイントは、item_idが文字列として扱われるため、「-」は他の文字列よりも小さい値として扱われます。
したがって、item_idが「-」の場合はそれが最小値として選択されます。

結果

このクエリを実行すると、各グループごとに最小のitem_idを持つ行だけが選択されます。
例えば、上記のサンプルデータの場合、以下のような結果が得られます。

category    | product_name | price  | supplier  | stock | location   | item_id
------------|--------------|--------|-----------|-------|------------|--------
Appliances  | Refrigerator | 299.99 | Supplier C| 50    | Warehouse 3| -
Electronics | Smartphone   | 599.99 | Supplier A| 100   | Warehouse 1| 1
Furniture   | Chair        | 49.99  | Supplier B| 200   | Warehouse 2| 1

まとめ

この方法を使用することで、SQLのSELECT句内で特定の列（この場合はitem_id）を使用せずに、特定の条件に基づいて最小値を持つ行を取得することができます。
サブクエリを活用することで、柔軟かつ効率的にデータを集約・フィルタリングすることができます。特定の値（この場合はitem_idが「-」）を特別な扱いにする必要がない場合、このように簡潔なクエリで目的を達成できます。

以上