順位を求めるｓｑｌ文について

質問

PL/pgSQLを使っています。

順位 score1 score2
----+------+-------
1 | 3 | 1
2 | 3 | 2
3 | 2 | 1
4 | 1 | 1

score1、score2はともに同じテーブル上にあり、
「score1が高いほどよく、同点の場合はscore2が低いほどよい」
というsql文はどうなりますでしょうか。
よろしくお願いいたします。

回答No.5

mojimojio · ベストアンサー

一般にSQLで順位を求めるには、「あるレコードのスコアより高いスコアのレコード数をカウントして+1する」という考え方をします。
この方法だと、ソート結果の行番号を使うよりも、同じスコアのレコードの順位が正しく求められる点で優れています。
ご質問のケースでは、順位付けの条件が少しだけ複雑になりますが、以下のようなSQLでレコードごとの順位が求められると思います。(特定レコードのみを対象にしたい場合、最後にwhere句を追加します)

select score1, score2, (select count(*) from hogetable t2 where t2.score1 > t1.score1 or (t2.score1 = t1.score1 and t2.score2 < t1.score2)) + 1 as rank from hogetable t1;

ただし、多数のレコードについて順位を求める場合、ソート結果を取得してプログラムで手続き的に処理した方が効率は良いです。(PL/pgSQLも使えます)

余談)
PL/pgSQLって、PostgreSQLの関数を記述する手続き言語のことで、この質問の場合でしたら、単にPostgreSQLと言った方が良いのではないでしょうか。

お礼

ありがとうございました。
PostgreSQLに限定されない一般的な考え方を教えていただき、
大変参考になりました。

>余談）

PL/pgSQLとPostgreSQLの違いもはっきりわからず混同していたので
こちらのご指摘も参考になりました。
ありがとうございます。

回答No.4

osamuy

順位の計算は、SQL一発というわけにはいかなくて、カーソルとかを使って、手続き的に求めてかなくてはいけないと思います。

回答No.3

mellowy

回答No.1 の mellowy です。

勘違いしてましたね。すみません。

環境がなくPL/pgSQLをあまり使ったことがないので
わからないのですが、以下のSQL文でいかがでしょうか？
間違っていたらごめんなさい。

SELECT ROWNUM, * FROM テーブル名 ORDER BY score1 DESC, score2 ASC

補足

PL/pgSQLでは、
ERROR: Attribute 'rownum' not found
になってしまうようです。
where句の中でテーブルを別名で呼べばよいのかなぁ、
というイメージがするのですが、具体的に思い浮かばなくて・・・。
本当に、たびたびありがとうございます。

回答No.2

MovingWalk

score1を降順、score2を昇順にして
select * from tbl order by score1 desc,score2;
でどうですか？

補足

ご回答ありがとうございました。
No1.の方にも補足させていただきましたが、
説明不足でした。
順位順にレコード検索するだけでなく、
順位を求める（付加する）方法が知りたかったのです。
おわかりになりましたら、よろしくお願いいたします。

回答No.1

mellowy

以下のSQLでどうでしょうか？
条件としては
・SCORE1, SCORE2 共に値（点）がある。
・SCORE1, SCORE2 は数値タイプかもしくは
　桁数固定の文字列タイプ
　※桁数３桁の場合
　　　 4 → 004
　　　10 → 010 など

SELECT * FROM テーブル名 ORDER BY score1 DESC, score2 ASC

補足

ご回答ありがとうございました。
説明不足でした。
順位順にレコード検索するだけでなく、
順位を求める（付加する）方法が知りたかったのです。
よろしくお願いいたします。