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: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: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では次のようなクエリで結果を得ることができるようです。

```sql: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: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: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では**一時テーブルを使えない**ため、この方法か物理テーブルを作成するしかなさそうです。
良い方法がありましたら、ご教授ください。