サブクエリーを使わずに二つのカラムをORDER BYし、さらに重複を除きたい

質問

以下のようなテーブルで、価格の高い順、重さの低い順、かつ種別の重複を除いた結果を求めたいと思っています。
（表はエディタ等にコピペしてご確認ください）

■商品テーブル
商品CD|種別CD| 価格 | 重さ
------+------+------+------
1| 1| 150| 12
------+------+------+------
2| 1| 110| 8
------+------+------+------
3| 2| 120| 11
------+------+------+------
4| 2| 105| 15
------+------+------+------
5| 2| 112| 7
------+------+------+------
6| 3| 110| 13
------+------+------+------
7| 3| 130| 5
------+------+------+------
8| 3| 90| 3
------+------+------+------
9| 4| 120| 6
------+------+------+------
10| 4| 120| 3
------+------+------+------
11| 4| 110| 10
------+------+------+------

■欲しい結果

商品CD|種別CD| 価格 | 重さ
------+------+------+------
1| 1| 150| 12
------+------+------+------
7| 3| 130| 5
------+------+------+------
10| 4| 120| 3
------+------+------+------
3| 2| 120| 11
------+------+------+------

サブクエリーを使用する場合は以下のように書けばよいことが分かりました。
SELECT * FROM (SELECT * FROM shohin ORDER BY KAKAKU DESC,OMOSA ) temp
GROUP BY SHUBETSUCD ORDER BY KAKAKU DESC,OMOSA

しかし、今回使用しているMySQLのバージョンが古いため、サブクエリーが使えません。サブクエリーを使用せずに同様の結果を求める方法はございますでしょうか？

ご存知の方がいらっしゃいましたら、ご教示いただければ幸いです。
宜しくお願い致します。

回答No.3

yambejp · ベストアンサー

一応こんな感じでやってみました。

//初期値設定
CREATE TABLE `X` (`商品CD` VARCHAR(10), `種別CD` VARCHAR(10), `価格` DOUBLE, `重さ` DOUBLE);
INSERT INTO `X` VALUES('1','1','150','12'),('2','1','110','8'),('3','2','120','11'),('4','2','105','15'),('5','2','112','7'),('6','3','110','13'),('7','3','130','5'),('8','3','90','3'),('9','4','120','6'),('10','4','120','3'),('11','4','110','10');

○パターン１
テンポラリーを使う場合、一応以下のものでご希望の
結果は得られます。ただしGROUP BYで集計された場合は、
集計フィールド以外は必ずしも値は保証されるわけでは
なさそうですので確実性をもとめるのならパターン２で。

CREATE TEMPORARY TABLE `Y` SELECT * FROM `X` ORDER BY `価格` DESC , `重さ` ASC;
SELECT * FROM `Y` GROUP BY `種別CD` ORDER BY `価格` DESC , `重さ` ASC;

○パターン２
テンポラリーで型を設定できるのが4.1以降のようなので
しょうがないので、テンポラリーをあきらめて、普通の
テーブルを使います。
この形式であれば、頭から流し込んで、プライマリで２番目
以降のヒットはエラーではじかれるのでデータは保証される
と思います。

CREATE TABLE `Z` (`商品CD` VARCHAR(10), `種別CD` VARCHAR(10) NOT NULL, `価格` DOUBLE, `重さ` DOUBLE, PRIMARY KEY (`種別CD`));
INSERT INTO `Z` SELECT * FROM `X` ORDER BY `価格` DESC , `重さ` ASC ,`商品CD` ASC;
SELECT * FROM `Z`;

結局、サブクエリーなり制限つきのテンポラリがつかえる
上位バージョンを使うか、必要に応じて適宜テーブルを作成し、
不要になったら削除するような仕組みをつくるかのいずれかが
現実的な方法で、もし両方気に入らなければ、
受け取ったミドルウェア（たとえばPHP）で２回目以降の
ヒットを無視するようにつくるのが妥当です。

お礼

ご返答ありがとうございます。

なるほど、テンポラリーの商品CDをPRIMARYにしてインサートすれば重複しなくなりますね。これは思いつきませんでした。

残念ながらMySQLのバージョンを上げることは現状では難しいので、上記の例を参考にさせて作成させて頂きます。

回答No.2

yambejp

ソートした結果をテンポラリーにおとしてから
group byすればいいかもしれません

回答No.1

chukenkenkou

同じ「価格と重さ」のものがあれば、商品CDと対応づけられません。

>サブクエリーを使用する場合は以下のように書けばよいことが分かりました。

このSQLは、MySQLにより「結果は保証されない」使い方になっています。

SELECT C1,C2,C3 FROM T1
GROUP BY C1

というように、GROUP BYで指定していない列名を、SELECTで選択することを、MySQLは拡張仕様として許しています。
→他の多くのRDBMSではエラーです

ただし、このような使用方法は、C1でグループ化すれば、C2、C3の値も一意に定まるだけであり、「一意にならないなら、結果は保証しない」と、マニュアルにも記載されています。

お礼

ありがとうございます。確かにご指摘のとおり、私の考えたクエリーではそもそも問題があるようでした。

本来の要件を満たすクエリーを作成することは可能でしょうか？