ソートした際の、特定のデータの番目を出したい

質問

例として、id,count というフィールドを用意し、下記のようなデータを使用します。
id count
A　5
B　2
C　3

このデータから、Aは ORDER BY count DESC で何番目なのか、Bは、Cは、といった形で、各々が何番目なのかを取得することは可能ですか？
また、可能でしたらどのような方法が考えられるでしょうか。

単純な例で言うと count で降順にソートした際の、id A と id C の番目を取得したい。
（Aは0・Cは1、番目が1からの場合 Aは1・Cは2といったデータ）
といったピンポイントでピックアップしていくような使い方をしたいと思っています。
また、取得後はPHPでデータを利用します。

少ないデータなら、全てのデータを取得してからPHPで振り分けも可能ですが、大量のデータがある際に効率的にできないかと質問させていただきました。

よろしくお願い致します。
-----------------
環境
　PHP 5.2.5
　MySQL 5.1.22

回答No.1

yone_sk · ベストアンサー

行番号を取得できれば
------------------------
SELECT ROW_NUM FROM
(SELECT [行番号] AS ROW_NUM, * FROM [テーブル] ORDER BY [ソート順])
WHERE
id in ('A', 'C')
------------------------
こんな感じでできると思いますが、
雰囲気つかめますでしょうか。

MySQLでの行番号の取得方法がわかりませんでした・・・orz

（oracle なら ROW_NUMBER関数 で取得できるのに・・・）

試してませんが

[行番号] を COUNT(id) + 1

にしてできませんかね＾＾；

お礼

ありがとうございます。

行番号というキーワードをもとに調べ、
当方の環境でoracle の ROW_NUMBER関数と同じ動作をさせるには、
変数を用意して自前で行番号を作るというのがありました。

試したのは以下です

SET @ROW_NUM = 0;
SELECT (@ROW_NUM:= @ROW_NUM + 1) AS ROW_NUMBER , `id`,`count` FROM `test`
WHERE id in('A','C')
ORDER BY `count` DESC;

これでA->1 C->2という結果が得られ、成功したと思ったのですが、
この番号は、どうやらソートだけに対しての番号ではなく、
WHERE節 も含んだ結果の番号になっていました。
ですので、WHERE id in('A','B')としても A->1 B->2 となってしまいます。

WHERE id in('A','B')の際に取得したいデータは A->1 B->3 というデータ（ソートされた際の順番）なのですが、
oracle の ROW_NUMBERですと、WHERE節を含まず得られますか？

TEMPORARY TABLEを使用して一度行番号を付けたテーブルを作成しそこからWEHER節を使うという方法もやってみましたが、
こちらも大量のデータを扱うとなると、効率・負荷的にどうなのかと疑問に思ってしまいました。

また、最初から行番号を付ける事も考えましたが、後からデータを一部削除することもあるので、番号のずれを直すのも手間になると思いやめました。

助言・ご教示いただければ幸いです。

回答No.2

yambejp

同順位がでたときに、繰り上げるか繰り下げるかで処理がことなります。
つまり、5のデータがあったとき3位が同順位だった場合、繰り下げなら「1,2,3,3,5」になり、繰り上げる場合は「1,2,3,3,4」となります。

仮にhogeテーブルに対して、普通に繰り下げる場合は

SELECT
(SELECT COUNT(*) +1 FROM hoge AS H2 WHERE H2.count < H1.count) AS RANK
,`H1`.*
FROM hoge AS H1

となります。

これにWHERE id='A'とかつければよいでしょう。

お礼

無事復旧しました。
実行時間が長かっただけだったかもしれません。

比べたところ、CREATE TEMPORARY TABLEで番号の付けた一時テーブルを作る方法が
当方の使用しているデータには最適ということがわかりました。
（抽出に0.1秒かかりませんでした）

ですので、そちらを利用したいと思います。
繰り下げ等のご考慮ありがとうございました。
（当方が実際に使用したかったのも同順位無しの内容です）

最後に、最終的な内容を以下に記述し、閉めたいと思います。

SET @ROW_NUM = 0;
CREATE TEMPORARY TABLE `TEMP`
SELECT (@ROW_NUM:= @ROW_NUM + 1) AS ROW_NUMBER , `test`.* FROM `test`
ORDER BY `count` DESC;
SELECT * FROM `TEMP`
WHERE id in('A','B')

補足

ありがとうございます。
サーバー側でPhpMyAdminから１万数千件くらいのデータに使用してみたところ、
最後に条件を付けてみても数秒かかってしまいました。

条件無しならどんくらいかかってしまうかと思い試してみたところ
負荷をかけすぎかサーバーのPhpMyAdminが機能しなくなってしまいました。

これから復旧に向け手立てを考えるので、
もう少し検証に時間がかかってしまいますのでご了承下さい。
（こんなことは初めてなので、どう復旧させるかも１からですが・・・）