空きのない順位(DENSE_RANK)をつける

 Accessには順位をつけるための関数がないので、通常はDCount関数やCount関数で代用することになります。
 さて、ここでは一歩進んで「空きのない順位」を求めるものとします。言い換えれば「順位が飛ばない」ということで、例えば同順位となる値が複数存在する場合にも、順位を「1,2,3,3,3,6,6,8…」などととつけるのではなく「1,2,3,3,3,4,4,5…」とつけていくことを意味します。

f:id:accs2014:20180520225616p:plain:right:w400

 データベースによってはDENSE_RANK関数として用意されていますが、Accessにはやはりありませんので、以下ではサブクエリで実現してみます。
 なお、用いるテーブルはこのようなものです。


f:id:accs2014:20180520225612p:plain:right:w600

 クエリのデザインビューです。
 求め方としては「より上位に位置する値が(いくつあるかではなく)何種類あるかを数えて1を足す」となります。
 これを踏まえて全体での順位と、所属ごとの順位をそれぞれ次のように求めます。

密順位: (SELECT Count([点数]) FROM (SELECT DISTINCT 点数 FROM 成績テーブル AS 成績テーブル_1) WHERE 成績テーブル_1.点数 > 成績テーブル.点数)+1
所属別密順位: (SELECT Count([点数]) FROM (SELECT DISTINCT 所属,点数 FROM 成績テーブル AS 成績テーブル_1) WHERE 成績テーブル_1.所属 = 成績テーブル.所属 AND 成績テーブル_1.点数 > 成績テーブル.点数)+1

 なお、これらの列を直接並べ替えの基準に指定しようとするとエラーになりますので「点数(降順)→番号(昇順)」というソート順にしています。


f:id:accs2014:20180520225608p:plain:right:w400

 データシートビューです。
 順位に空きがなく、きちんと詰まっていることがわかります。


f:id:accs2014:20180520225605p:plain:right:w400

 「所属(昇順)→点数(降順)→番号(昇順)」というソート順にするとこのようになります。


 なお、通常の(空きのある)順位をつける例(Dcount関数を利用)については次の記事をご覧ください。

www.accessdbstudy.net

 また、上記で述べているように、空きのない順位は「値の種類を数える」ということにより求められます。これについては次の記事でも扱っています(ほとんど同内容ですが)。

www.accessdbstudy.net