このようなデータがあったとして、
id | content
----+---------
1 | 渋谷
2 | 恵比寿
3 | 五反田
4 | 渋谷
5 | 中目黒
contentがユニークな下記のようなデータを取得したいとします。
id | content
----+---------
1 | 渋谷
2 | 恵比寿
3 | 五反田
5 | 中目黒
mysqlに慣れたいた自分は下記のようなsql文を発行。
select * from result group by content;
すると
ERROR: column "result.id" must appear in the GROUP BY clause or be used in an aggregate function
行 1: select * from result group by content;
ふむ。result.idはgroup by節に現れるか、集計関数として使われなければならない。
後者のほうはイマイチ意味がわからないので、group by節に追加。
select * from result group by id, content;
id | content
----+---------
3 | 五反田
5 | 中目黒
1 | 渋谷
4 | 渋谷
2 | 恵比寿
(5 行)
んん？予想した結果と違う。
ここで色々ぐぐってみると要するにgroup byした結果、id = 1の渋谷とid = 4の渋谷どちらを表示すればよいかわからないのでエラー、ということらしい。
なるほど、たしかに。
こうゆうときはdistinct句を使うと "一番最初の行" を取得してくれるから、これを使うと良いらしい。
SELECT DISTINCT ON ( expression [, ...] )は各行集合の中で、指定した式が等しいと評価した最初の行のみを保持します。
最初の行ということは id = 1の渋谷、ですかね。
select distinct on (content) * from result;
id | content
----+---------
5 | 中目黒
3 | 五反田
2 | 恵比寿
1 | 渋谷
(4 行)
id = 1の予想はあっていましたが、順番が逆順になっています。
じゃぁこうしよう。
select distinct on (content) * from result order by id asc;
またエラー。
ERROR: SELECT DISTINCT ON expressions must match initial ORDER BY expressions
行 1: select distinct on (content) * from result order by id;
^
distinct onの条件式はorder byの一番最初の条件式とマッチする必要がある、とのこと。
え、、こうゆうこと？
select distinct on (content) * from result order by content, id asc;
id | content
----+---------
5 | 中目黒
3 | 五反田
2 | 恵比寿
1 | 渋谷
(4 行)
エラーこそ出ないもののちがう。。
ここでdistinct句のリファレンスをもう一度読んでみる。
各集合の"最初の行"は、ORDER BYを使用して目的の行が確実に最初に現れるようにしない限り予測することはできないことに注意してください。
なるほど・・。つまりここで使っているorder byはid = 1の渋谷とid = 4の渋谷のどちらを選ぶか、ってゆう条件に使用されているのかな。たとえば前述の式をasc→descにしてみる。
select distinct on (content) * from result order by content, id desc;
id | content
----+---------
5 | 中目黒
3 | 五反田
2 | 恵比寿
4 | 渋谷
(4 行)
ほら、id = 4のほうになった。つまりdistinct onとorder byはセットで使うべきなのですな。となると、この結果をサブクエリにして再度idでorderしてやるくらいしか思いつきません。
select * from (select distinct on (content) * from result order by content, id asc) as distincted_table order by id asc;
id | content
----+---------
1 | 渋谷
2 | 恵比寿
3 | 五反田
5 | 中目黒
(4 行)
できました〜パチパチ。
とはいえdistinct_onでなくgroup byでやる方法はないものか。