How to Get Rank in Access Query?

Question

Hello all,

I want to Get Rank according to Total but Rank should not be skipped.

For Example if two person Get 1 rank then 3rd should be started with 2 not 3.

please Help me with this Query.

I am using Microsoft Access as my back-end tool.

My Query is like this

SELECT 
       (SELECT COUNT(T1.*) + 1
          FROM
                 [tbl1] AS T1 
         WHERE T1.Total > T2.Total ) AS Rank 
FROM 
      [tbl2] AS T2 
order by T2.Total

Thank u...

Answer 1

This is called dense rank:

You need to calculate the rank for the measure and join the additional rows. E.g.

SELECT  O.* ,
        ( SELECT    COUNT(*)
          FROM      ( SELECT    T.Total
                      FROM      Table1 T
                      GROUP BY  T.Total
                    ) I
          WHERE     I.Total >= O.Total
        ) DenseRank
FROM    Table1 AS O
ORDER BY O.Total;

Answer 2

Thank u...:)

Answer 3

Nice one Stefan.  Just to be clear, basically this is like counting rows in Excel, right.

---

MY BOOK

Answer 4

Yes.

The most inner SELECT with the GROUP BY returns the unique values. This assures that we get the dense rank. Without GROUP BY we get the normal rank.

Then we evalutate the rank by counting the rows. Caveat: There was a typo in m original answer, I used an = instead of >=.