SQLでグループ内の最新の値を返す方法

SQL(PostgreSQL)でグループごとにそれぞれ最新の値を返す方法です。

テーブルの定義とデータの用意をします。

CREATE TABLE fruits (
    id SERIAL,
    name TEXT,
    price INTEGER,
    CONSTRAINT fruits_id_pkey PRIMARY KEY (id)
);
CREATE INDEX fruits_name ON fruits (name);

INSERT INTO fruits (name, price) VALUES ('りんご', 120);
INSERT INTO fruits (name, price) VALUES ('ばなな', 50);
INSERT INTO fruits (name, price) VALUES ('もも', 150);
INSERT INTO fruits (name, price) VALUES ('りんご', 100);
INSERT INTO fruits (name, price) VALUES ('ばなな', 80);

テーブルの定義とテーブルの中身を確認します。

\d fruits
                         Table "public.fruits"
 Column |  Type   |                      Modifiers
--------+---------+-----------------------------------------------------
 id     | integer | not null default nextval('fruits_id_seq'::regclass)
 name   | text    |
 price  | integer |
Indexes:
    "fruits_id_pkey" PRIMARY KEY, btree (id)
    "fruits_name" btree (name)


SELECT * FROM fruits ORDER BY id;
 id |  name  | price
----+--------+-------
  1 | りんご |   120
  2 | ばなな |    50
  3 | もも   |   150
  4 | りんご |   100
  5 | ばなな |    80
(5 rows)

りんご、ばなな、ももについて、最新(idが最も大きい)レコードのpriceを取得します。

SELECT
    name,
    price
FROM fruits
WHERE id IN (
    SELECT MAX(id)
    FROM fruits
    GROUP BY name
);

  name  | price
--------+-------
 もも   |   150
 りんご |   100
 ばなな |    80
(3 rows)

DISTINCT ONを使うとよりうまく書けます。

SELECT DISTINCT ON(name)
    name,
    price
FROM fruits
ORDER BY
    name,
    id DESC
;
  name  | price
--------+-------
 もも   |   150
 ばなな |    80
 りんご |   100
(3 rows)

コメント

2015 - 2017 (c) 成瀬基樹