開発メモ,主に補足by子連れ親父プログラマー

2010-04-17

DISTINCT と ORDER BY を一緒に使う時の、PostgresとMySQLの違い

今やってるやつで、出てきた現象なのでちょっとメモ。
例えば items というようなテーブルがあって、id と name がある。
 id |  name
----+--------
  1 | test
  2 | sample
で、この items には複数のカテゴリーとサブカテゴリーが紐づけられている。
紐づけ用のテーブルは items_categories というようなもので、こんな感じになっている。
 item_id | category_id | sub_category_id | orders
---------+-------------+-----------------+--------
       1 |           1 |               1 |      2
       1 |           1 |               2 |      2
       2 |           1 |               2 |      1
アイテム1はサブカテゴリー1と2に、アイテム2はサブカテゴリー2に紐付いていて、どちらもカテゴリーは1だが、
そのカテゴリー1の中での並ぶ順番が orders ということで設定されている。
アイテム2が順番は1、アイテム1が2番と。

で、こいつをまず順番関係なしに、join してみると
SELECT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
こうなる。
 item_id |  name
---------+--------
       1 | test
       1 | test
       2 | sample
orders 順にしたいので、
SELECT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders
とすると、
 item_id |  name
---------+--------
       2 | sample
       1 | test
       1 | test
これは動く。item_id が重複してるので、distinct を使うと、
SELECT DISTINCT ic.item_id, i.name 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders
Postgresの場合、エラーになる。
ERROR:  for SELECT DISTINCT, ORDER BY expressions must appear in select list
MySQLの場合、
 item_id |  name
---------+--------
       2 | sample
       1 | test
出る。
Postgresでも
SELECT DISTINCT ic.item_id, i.name, ic.orders 
FROM items_categories ic 
INNER JOIN items i ON ic.item_id=i.id
WHERE ic.category_id=1
ORDER BY ic.orders
とすればちゃんと出る。
 item_id |  name  | orders
---------+--------+--------
       2 | sample |      1
       1 | test   |      2
そりゃ、おかしいのはMySQLでしょう。この場合。

このブログを検索

Powered by Blogger.

ラベル

php (17) jQuery (13) OSX (10) MySQL (8) Javascript (7) Postgres (7) port (7) apache (6) Java (3) Smarty (2) html (2) pear (2) FCKEditor (1) XAMPP (1) css (1) git (1) perl (1) ruby (1)

Facebookバナー