ACCESS 2013 RANK関数の代わりにDCOUNT関数を使ってグループごとに順位を出す

Question

RANK関数を使った処理をしたかったのですが、
ACCESSではRANK関数が使用できないので、
代わりにDCOUNT関数を使ってグループごとに順位を出したいと思っています。
条件の指定の所が分からず困っています。
DCOUNT関数以外のものを使うのでしょうか？
よろしくお願いします。

テーブル1

ID
OSK01
OSK04
KOB01
KOB03
OSK02

※アルファベット３文字＋数字２文字の固定

クエリ1

ID	GROUP	順位
OSK01	OSK	1
OSK04	OSK	3
KOB01	KOB	1
KOB03	KOB	2
OSK02	OSK	2

SELECT ID, Left([ID],3) AS [GROUP], DCount("ID","テーブル1", 条件部分？) AS 順位 FROM テーブル1;

※GROUPごとの順位を出したいです。
GROUPは、IDの最初の３文字が同じものになります。
よろしくお願いします。

Answer 1

IDフィールドが主キーだったりインデックスが設定されているなら、
条件式内のIDフィールドは関数内にいれないほうがインデックスが使えるので
高速化が期待できます。

SELECT
    ID, Left([ID], 3) AS [GROUP],
    (select Count(*)
     from テーブル1
     where
       [ID] Like Left([t1].[ID], 3) & "*"
       And [ID] <= [t1].[ID]
    ) AS 順位
FROM
    テーブル1 as t1;

件数が多くて上記でも重いという場合は、連番フィールドを追加してVBAでそこに入力するという方法も検討するといいでしょう。
下記で汎用関数を紹介していますのでご参考に。

グループ毎連番を自動入力する関数 - hatena chips

Comments

[mnbwqz]

仰る通り、likeを使うと[ID]で関数を使わなくてよくなりますね。
気づきませんでした。アイデアをありがとうございました。
ACCESSではVBAが使えるので、
「グループ毎連番を自動入力する関数」を検討します。

Answer 2

SELECT ID, Left([ID],3) AS [GROUP]
     , DCount("ID","テーブル1", "Left([ID],3)='" & Left([ID],3) & "' And " & "Mid([ID],4)<='" & Mid([ID],4) & "'") AS 順位 
FROM テーブル1;

Dcount()は低速なので、その場合サブクエリーにすると概ね改善されます。

SELECT ID, Left([ID],3) AS [GROUP]
     , (select Count(*) from テーブル1 
        where Left([ID],3) = Left([t1].[ID],3) 
          And Mid([ID],4)<= Mid([t1].[ID],4) 
       ) AS 順位 
FROM テーブル1 as t1;

Comments

[mnbwqz]

ありがとうございます。
１番目のDCOUNT関数、動きました。
確かにおっしゃる通り遅いですね。
２番目のサブクエリも動作しました。
ありがとうございました。