Rank Query Gap??? (1 Viewer)

[g-richardson]

I have set up two queries to generate a rank order based volume generated. The query does what I want for the most part in that if two parties have the same volume, they have a common rank (this is what I want to have happen). The problem comes that at various points throughout the data set, some of the rank sequence will be skipped...example, rank 265 to 267 (skips 266) and rank 786 to 790 (skips 787, 788, 789).

How can I close the gaps?

Here are the two queries...

Query 1
SELECT VolRnkALL.group_code, Sum(VolRnkALL.avg_group_volume) AS SumOfAvg_Group_Volume
FROM VolRnkALL
GROUP BY VolRnkALL.group_code
ORDER BY Sum(VolRnkALL.avg_group_volume) DESC;

Query 2
SELECT qry_SetRankSTEP1.group_code, qry_SetRankSTEP1.SumOfAvg_Group_Volume AS Expr1, (SELECT Count(*) FROM qry_SetRankSTEP1 t1

WHERE
t1.SumOFAvg_group_volume >= qry_SetRankSTEP1.SumOFAvg_group_volume) AS Rank
FROM qry_SetRankSTEP1;

Thanks for your help

 

[Jon K]

Alternative method of handling ties in ranking
see Example 3

http://support.microsoft.com/default.aspx?scid=kb;en-us;208946

.

 

[g-richardson]

This did the trick. Thanks Jon!