# 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.

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...

• Edited by Thursday, April 14, 2016 10:19 AM
• Moved by Thursday, April 14, 2016 2:08 PM Office related
Thursday, April 14, 2016 10:16 AM

• 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;
```

Thursday, April 14, 2016 12:24 PM

### All replies

• 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;
```

Thursday, April 14, 2016 12:24 PM
• Thank u...:)
Friday, April 15, 2016 8:25 AM
• Nice one Stefan.  Just to be clear, basically this is like counting rows in Excel, right.

MY BOOK

Sunday, April 17, 2016 3:37 AM
• 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 >=.

Sunday, April 17, 2016 11:08 AM