group byを利用したＳＱＬの書き方

質問

前にもＳＱＬの書き方について質問したのですが、どうも思いどおりの結果になりません。知恵をお貸しください。

このようなテーブルiq_resultが例えばあります。

handle time cat sex datetime,generation
もん 00:00:37 ハーフ 女 2006-11-02 20
まさ 00:00:43 ハーフ 男 2006-11-04 11:44:56 30
まさ 00:00:45 ハーフ 男 2006-11-04 11:28:59 40
名無しの権兵衛 00:00:43 ハーフ 男 2006-11-06 40
スズキ 00:00:44 ハーフ 女 2006-10-26 50
まさ 00:00:47 ハーフ 男 2006-10-26 17:53:30 30

これを「同じhandleがあった場合は、その人の一番timeが小さい
データを採用し」かつ
「timeが小さい順（同じ場合はdatetimeが小さい順）に並べ替えて」順位表を出したく思っています。
期待する結果は
もん 00:00:37 ハーフ 女 2006-11-02 20
まさ 00:00:43 ハーフ 男 2006-11-04 11:44:56 30
名無しの権兵衛 00:00:43 ハーフ 男 2006-11-06 40
スズキ 00:00:44 ハーフ 女 2006-10-26 50
になると思うのですが、
SELECT handle, min(time) as pnt, cat ,sex,datetime,generation,team FROM iq_result where cat = 'ハーフ' group by `handle` order by pnt , datetime
または
SELECT handle, min(time) as pnt, cat ,sex,min(datetime) as pnt2,generation,team FROM iq_result where cat = 'ハーフ' group by `handle` order by pnt , pnt2
でも以下のようになってしまいます。
もん 00:00:37 ハーフ 女 2006-11-02 20
まさ 00:00:43 ハーフ 男 2006-10-26 30
名無しの権兵衛 00:00:43 ハーフ 男 2006-11-06 40
スズキ 00:00:44 ハーフ 女 2006-10-26 50

つまり複数ある「まさ」のデータはtimeが最小の00:00:43を選ぶのですがそれに付随するdatetimeを別の列？から持ってきてしまいます。

何がおかしいのでしょうか？

回答No.6

kazuhisa01 · ベストアンサー

よく考えるとこれってMin(_Time)の意味ってないんですよね・・・。
_DateTimeに時間まで入っているので100％グループ化できない。
結果下のSQL文でもいいという話です。

SELECT Handle, _Time, Cat, Sex, _DateTime AS MinDateTime, Generation
FROM iq_result
WHERE Cat = 'ハーフ'
ORDER BY Handle, _Time, _DateTime

結局何だったんだろうという結果に(涙)

あとは登録するデータを日にちだけか時間まで入れるかでSQL文でうまく持ってこれるようになるかもしれません。

頑張って下さい。
今回はあまりお力になれなくてすみませんでした。

お礼

長い間、お付き合いいただいてありがとうございました。
もう少し勉強してみます。

また、困った時はよろしくお願いいたします。

回答No.5

kazuhisa01

あぁぁぁ。
回答してから気づきました。
一個下の回答はなかったことに・・・・。

まさのデータ。
確かに可笑しいですね。
この状況から見ると、Generationが30でTimeの最小値とDateTimeの最小値を持ってきているので、SQL文が可笑しいですね。

なので後はこの方法しか残っていないかもしれません。

＜SQL文＞
SELECT Handle, MIN(_Time) AS MinTime, Cat, Sex, _DateTime AS MinDateTime, Generation
FROM iq_result
GROUP BY Handle, Cat, Sex, Generation, _DateTime
HAVING (Cat = 'ハーフ')
ORDER BY Handle, MIN(_Time), _DateTime

＜取得結果＞
スズキ 0:00:44 ハーフ 女 2006/10/26 50
まさ 0:00:43 ハーフ 男 2006/11/04 11:44:56 30
まさ 0:00:45 ハーフ 男 2006/11/04 11:28:59 40
まさ 0:00:47 ハーフ 男 2006/10/26 17:53:30 30
もん 0:00:37 ハーフ 女 2006/11/02 20
名無しの権兵衛 0:00:43 ハーフ 男 2006/11/06 40

＜方法＞
まさのデータが3件でてきますが、欲しいデータは1番上だけだとおもうので、IF文でまさのデータを1回だけ取得して配列変数に格納していく方法です。
以下はVB.NETの場合のサンプルです。

strHandle = ""
If ADORS.Filde("Handle").Value <> strHandle Then
※ここでデータを変数に格納
strHandle = ADORS.Filde("Handle").Value
End If

staHandleはString変数です。
あとはADORS.EOF = TrueになるまでLOOPしてあげれば変数の中には期待したデータが取れてるはずです。

お礼

回答ありがとうございます。試してみました。
ご指摘の通りの結果がでました。
この次に同じHandleを消していって、再びソートっていう感じ
なのですね。
意外とSQL文って融通が利かない・・・ですね。（泣）

回答No.4

kazuhisa01

＃１です。
おかしいですね。

SELECT Handle, MIN(_Time) AS MinTime, Cat, Sex, MIN(_DateTime) AS MinDateTime, Generation
FROM iq_result
GROUP BY Handle, Cat, Sex, Generation
HAVING (Cat = 'ハーフ')
ORDER BY Handle, MIN(_Time), MIN(_DateTime)
※ORDER BY に Handle が抜けてました。

を流すと、こちらの環境だとしっかりまさのデータが2件出てきたのですが・・・。

結果
スズキ 0:00:44 ハーフ 女 2006/10/26 50
まさ 0:00:43 ハーフ 男 2006/10/26 17:53:30 30
まさ 0:00:45 ハーフ 男 2006/11/04 11:28:59 40
もん 0:00:37 ハーフ 女 2006/11/02 20
名無しの権兵衛 0:00:43 ハーフ 男 2006/11/06 40

上の結果が返ってきたので、IFでまさの時間が早い方を取るだけだったのですが・・・。

テーブルのデータは以下の通りなんですよね。

handle time cat sex datetime,generation
もん 00:00:37 ハーフ 女 2006-11-02 20
まさ 00:00:43 ハーフ 男 2006-11-04 11:44:56 30
まさ 00:00:45 ハーフ 男 2006-11-04 11:28:59 40
名無しの権兵衛 00:00:43 ハーフ 男 2006-11-06 40
スズキ 00:00:44 ハーフ 女 2006-10-26 50
まさ 00:00:47 ハーフ 男 2006-10-26 17:53:30 30

もう一度、質問者さんのデータを確認して頂いたほうが良いかもしれません。

ごめんなさい。
前回の上のSQL文だと取得結果が間違っていました。
あのままだと一致するまさのデータが見つからないです＾＾；

回答No.3

kazuhisa01

再び＃１です。
確かにこのような用途はありえますが、今回ネックになっているのは
まさ 00:00:45 ハーフ 男 2006-11-04 11:28:59 40
こいつですｗ
このまさのデータだけGenerationが40なのです。
だからSQLの結果がうまくまとまらないのですよね。

SQL文1回で思った条件を持ってこないといけないのでしょうか。
今のデータベースの設計上1回でというのは難しいので、期待している結果は数回に分ければ取ってこれそうですが。

1回目
SELECT Handle, MIN(_Time) AS MinTime, MIN(_DateTime) AS MinDateTime
FROM iq_result
GROUP BY Handle

2回目以降
1回目の結果をWHEREで設定してデータの取得。
※但し、ここはLOOP文になるので件数文通ることになります。

これなら取得できると思いますが・・・処理に時間がかかりそうですね。

あとは、SQLは

SELECT Handle, MIN(_Time) AS MinTime, Cat, Sex, MIN(_DateTime) AS MinDateTime, Generation
FROM iq_result
GROUP BY Handle, Cat, Sex, Generation
HAVING (Cat = 'ハーフ')
ORDER BY MIN(_Time), MIN(_DateTime)

を使用して、結果をIFなどで比較していく。

こちらの方が先に説明した方法より早いと思います。

自分もなんかスッキリしませんが、早めの解決をしたければSQL1回はあきらめた方がよさそうです。

お礼

すばやい回答ありがとうございました。
まず、２番目に書かれた方法で検索してみたのですが
やはり
まさ 00:00:43 ハーフ 男 2006-10-26 30
が出てきてしまいます。00:00:43のデータは 2006-11-04 11:44:56 30
なのに・・泣
これだとＩＦでの比較のしようもないかと・・。
ＳＱＬは１度でなくてもＯＫです。とにかく、正しい結果が導き
だせれば・・・なんですが。

１番目の回答例は具体的にどうやるのでしょうか？
お手数をかけて申し訳ございません。

回答No.2

kazuhisa01

＃１です。
Group Byには表示する項目でMin、Maxなどを行わない項目を書く必要がありました。
ただ、今回のデータだと以下のSQL文を実行すると結果が質問者さんと異なります。

条件：TimeとDateTimeはプライマリー規約で使用できないため、頭に_を入れてあります。
SQLはMSDN（簡易SQL Server）での実行を行っています。

SELECT Handle, MIN(_Time) AS MinTime, Cat, Sex, MIN(_DateTime) AS MinDateTime, Generation
FROM iq_result
GROUP BY Handle, Cat, Sex, Generation
HAVING (Cat = 'ハーフ')
ORDER BY MIN(_Time), MIN(_DateTime)
※WHERE条件がHAVINGになっているが抽出結果に変わりはない。

だと

もん 0:00:37 ハーフ 女 2006/11/02 20
まさ 0:00:43 ハーフ 男 2006/10/26 17:53:30 30
名無しの権兵衛 0:00:43 ハーフ 男 2006/11/06 40
スズキ 0:00:44 ハーフ 女 2006/10/26 50
まさ 0:00:45 ハーフ 男 2006/11/04 11:28:59 40

という結果が返ってきました。

上記データを見る限り、まさのデータを1つにまとめるのは難しいと思われます。

前回のSQLがエラーになってしまったので、お詫びと参考意見を書かせて頂きました。
お力になれず、申し訳ありません。

お礼

書き込みありがとうございます。
うーん、どうやったらいいのか・・。
こういう用途ってあるような気がするのですが、
データベースの設計に無理があるのですかね？

回答No.1

kazuhisa01

SELECT handle, min(time) as pnt, cat ,sex,min(datetime) as pnt2,generation,team FROM iq_result where cat = 'ハーフ' group by pnt,pnt2 order by pnt , pnt2

かな。

datetimeもGroupByしてみて下さい。

お礼

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

supplied argument is not a valid MySQL result
というエラーが出てしまいます。GroupByするのは
handleじゃなくて・・ですか？