Fix Ranking Query

[Pipi]

In the query, ties are given the same rank:

1.Salesperson Division NumberSold Rank
2. Robert Electronics 99 2
3. Jenny Electronics 54 4
4. Billy Appliances 54 4
5. Karen Appliances 102 1
6. Kim Appliances 30 5

So in this case Jenny and Billy are both ranked 4.
But actually they should both be ranked 3
Then nobody is 4 and Kim is 5.

Any fast way of fixing this ?

Thanks for the great post though !

[Rabbit]

Take out the equal sign and add one to the count.

[zmbd]

Relocated this post from Rabbit's insight article located at:
Ranking Queries in MS Access SQL

If you are still having issues with this after Rabbit's suggestion we may need to see your SQL.

[NeoPa]

Hi Rabbit.

You may want to update your article to incorporate this. Pipi's point is a good one. Two equal values that are both less than the top two values are generally given a ranking of 3 and not 4.

[Rabbit]

Good idea, I'll do so