テーブル操作でIDを除いて一意の値を取得する必要がありました。
DISTINCTやGROUP BYをする時にIDを含めてしまうとIDが一意の値のため重複の削除ができず、この場合IDを取得できないのかと思っていました。
しかし、先人の方のSQLをみてみるとIDもついてました。こんなやり方でできるのかと感動したので備忘録です。
重複の件数を出す方法はこちらにまとめています。
動作環境
この記事はSQL FIDDLEでMySQL5.6、PostgreSQL9.6、SQLiteで動作確認しました。
下記文章は主にPostgreSQLで仕様で調べています。
重複をなくしIDを取得し表示する方法
GROUP BYで重複をなくし(一意)IDをつけて表示します。IDは重複のため重複の中から1つ条件にあったものを取得し表示させます。
準備
テストするテーブルの情報です。
| ItemID | ItemName | Quantity | AREA |
|---|---|---|---|
| 1 | プリン | 25 | 大阪 |
| 2 | ゼリー | 20 | 大阪 |
| 3 | ゼリー | 30 | 東京 |
| 4 | プリン | 15 | 大阪 |
| 5 | プリン | 60 | 東京 |
| 6 | プリン | 25 | 大阪 |
| 7 | ゼリー | 20 | 東京 |
上記テーブルの作成用コードです。
— CREATE文 item テーブル
CREATE TABLE item (
ItemID numeric(8, 0) unique not null,
ItemName character varying (20),
Quantity numeric(8, 0),
AREA character varying (20),
primary key(ItemID)
);
— itemテーブル用INSERT文
insert into item
values(0001, ‘プリン’, 25, ‘大阪’),
(0002, ‘ゼリー’, 20, ‘大阪’),
(0003, ‘ゼリー’, 30, ‘東京’),
(0004, ‘プリン’, 15,’大阪’),
(0005, ‘プリン’, 60, ‘東京’),
(0006, ‘プリン’, 25, ‘大阪’),
(0007, ‘ゼリー’, 20, ‘東京’);
重複をなくし(一意)IDを取得し表示するサンプルSQL
SELECT
ItemName
,AREA
,Max(ItemID) as ItemID — ItemIDの中から最大値を取得
FROM item
Group by
ItemName
,AREA
Order by
ItemID
;
MAX()でIDの中から大きい数字を取得して割り当てています。MIN()にすると小さい数字(若番)を割り当てます。
■ 結果
| ItemName | AREA | ItemID |
|---|---|---|
| ゼリー | 大阪 | 2 |
| プリン | 東京 | 5 |
| プリン | 大阪 | 6 |
| ゼリー | 東京 | 7 |
おわり
とても簡単にIDが取得できました。いくらググっても出てこないからできないのかと思いましたが考え方の問題でした。先人の書いたコードは勉強になります。
関連：Max()については以下でまとめています。
追記
PostgreSQLであればより簡単に重複をなくしIDを取得することができそうです。
