SQL Server CEでRANK関数と同じ結果を得るには

はじめに

SQL Server Compact Edition(SQL Server CE)でRANKが使えなかったため試行錯誤した際のメモです。

結論

次のようなテーブルがあるとします。

member

id	name	score
001	Satou	60
002	Suzuki	45
003	Takahashi	80
004	Tanaka	50
005	Itou	20
006	Watanabe	40
007	Yamamoto	30
008	Nakamura	55

scoreの高い順に並び替え、順位付けします。
RANK関数を使う場合はこのようになります。

SQL

SELECT id, name, score, 
       RANK() OVER(ORDER BY score DESC) rank_result
FROM member

次の結果が得られます。

id	name	score	rank_result
003	Takahashi	80	1
001	Satou	60	2
008	Nakamura	55	3
004	Tanaka	50	4
002	Suzuki	45	5
006	Watanabe	40	6
007	Yamamoto	30	7
005	Itou	20	8

SQL Server CEでは、次のようにします。

SQL_CE

SELECT a.id, a.name, a.score,
       COUNT (b.score) rank_result
FROM member a, member b
WHERE a.score < b.score OR (a.score = b.score AND a.id = b.id)
GROUP BY a.id, a.name, a.score
ORDER BY a.score DESC, a.id, a.name

ポイント

RANK関数を使わずに順位付けをしたいシチュエーションとしては、主にMySQLなどの環境が多いようです。
MySQLでは次のようなクエリで結果を得ることができるようです。

MySQL

SELECT id, name, score,
       (SELECT COUNT (*) 
        FROM member a 
        WHERE member.score < a.score OR (member.score = a.score AND member.id = a.id)) rank_result
FROM member
ORDER BY score DESC

MySQL版クエリの参考: https://johobase.com/rank-not-use-function-sql/

検索でまず得られたクエリはこちらでした。
同じテーブルを比較し、scoreが自分より大きいレコードが何件あるか+自分と同じレコードを順位として算出するという方法です(元記事では自分と同じレコードは1件という前提となっています)。
しかし、CEで実行しようとすると、Token line number = 2, Token line offset = 9, Token in error = SELECT となり、エラーとなってしまいます。
CEではスカラー値を返すサブクエリを使えないのが原因のようです。
解決方法としては、SELECT句の中でサブクエリを使用するのではなく、テーブルの結合で実装。
どうしてもサブクエリを使う必要がある場合は、FROM句の中で使い、テーブルとして値を返すようにしましょう。

CEでのサブクエリについての詳細: https://stackoverflow.com/questions/470009/how-can-i-make-this-query-in-sql-server-compact-edition/648038#648038

おまけ

scoreにあたる部分を別テーブルから集計する場合はどのようになるでしょうか。
このようなテーブルがあり、先ほどと同じ結果を得たいとします。

member_list

id	name
001	Satou
002	Suzuki
003	Takahashi
004	Tanaka
005	Itou
006	Watanabe
007	Yamamoto
008	Nakamura

score_log

id	score
001	30
001	30
002	45
003	80
004	50
005	20
006	40
007	30
008	55

id, name, scoreのテーブルを得るために、member_listとscore_logを結合する必要があります。

SQL_CE_サブクエリ

SELECT member_list.id, member_list.name, SUM(score_log.score) score
FROM member_list LEFT JOIN score_log ON member_list.id = score_log.id
GROUP BY member_list.id, member_list.name

これで、このページで最初に使ったmemberと同じテーブルが取得できました。
これをサブクエリとして、先ほどのクエリに当てはめるとこのようになります。

SQL_CE

SELECT a.id, a.name, a.score,
       COUNT (b.score) rank_result
FROM (SELECT member_list.id, member_list.name, SUM(score_log.score) score
      FROM member_list LEFT JOIN score_log ON member_list.id = score_log.id
      GROUP BY member_list.id, member_list.name) a, 
     (SELECT member_list.id, member_list.name, SUM(score_log.score) score
      FROM member_list LEFT JOIN score_log ON member_list.id = score_log.id
      GROUP BY member_list.id, member_list.name) b
WHERE a.score < b.score OR (a.score = b.score AND a.id = b.id)
GROUP BY a.id, a.name, a.score
ORDER BY a.score DESC, a.id, a.name

もうちょっとスマートに書きたい……！！

可読性が著しく下がる上、同じ問い合わせを2回しなければいけないというのがなんとも言えない感じですが、CEでは一時テーブルを使えないため、この方法か物理テーブルを作成するしかなさそうです。
良い方法がありましたら、ご教授ください。