PostgreSQL の DISTINCT ON
PostgreSQL には 通常の DISTINCT ではない DISTINCT ON というものがあります。
次のようなテーブルがあるとします。
db1=# SELECT * FROM distinct_test;
field1 | field2 | field3
--------+--------+--------
1 | 4 | 1
1 | 4 | 2
2 | 2 | 1
2 | 2 | 2
3 | -1 | 1
3 | -1 | 2
(6 rows)
まずは通常の DISTINCT です。
db1=# SELECT DISTINCT field1, field2
FROM distinct_test;
field1 | field2
--------+--------
1 | 4
2 | 2
3 | -1
(3 rows)
この場合、列に指定した field1, field2 の順番でソートされます。
列の指定の順番を変えます。
db1=# SELECT DISTINCT field2, field1
FROM distinct_test;
field2 | field1
--------+--------
-1 | 3
2 | 2
4 | 1
(3 rows)
今度は field2, field1 でソートされます。
これはまぁ、こういうものです。
DISTINCT ON を使ってみます。
DISTINCT ON (field1, field2) のカッコの中と
列の指定 "field1, field2, field3" が
違うことに注意してください。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows)
行数は DISTINCT で field1, field2 を指定したときと同じです。
つまり DISTINCT ON を使用すると カッコの中で指定した列で まとめられ、カッコの中で指定していない列（この例だと field3 ）は 最初に見つかったものが出力されます。
「 DISTINCT でまとめたいけど、他の列も欲しい！」というときに便利ですね。 ただ、このままだと DISTINCT ON に指定していない列は どの値が出るのか保障されないため、ソートをかけてやる必要があります。
ORDER BY によるソートは次のようになります。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 1 2 | 2 | 1 3 | -1 | 1 (3 rows) db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field2; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field3; ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
DISTINCT ON の先頭で指定されている field1 以外の 列では エラーになってしまいます。
ORDER BY でソートするには、 DISTINCT ON で指定した列（指定順）の後に、DISTINCT ON 以外の列を書く必要があります。
db1=# SELECT DISTINCT ON (field1, field2) field1, field2, field3 FROM distinct_test ORDER BY field1, field2, field3 DESC; field1 | field2 | field3 --------+--------+-------- 1 | 4 | 2 2 | 2 | 2 3 | -1 | 2 (3 rows)
こうすることによって GROUP BY で MAX() を使ったのと 同じ値を取得すことができました。
