Re: Ranking - dealing with ties

Sophie wrote:
a) I didn't think until too late just to post the SQL - sorry. In any case,
I was able to use your post of Feb 17 to solve this problem. The SQL shown
below gives perfect results every time after numerous tests. FWIT, the '+1'
you asked about ensures the results look like 1,2T,2T,3 etc instead of
0,1T,1T,2...

qryDivScore has fields Score (Desc) and Div (Asc).
qryDistinctDivScore has field Score (Desc), with the Unique Value property
set.

I'm mildy surprised that this actually works because I had to refer to
qryDivScore using two different Aliases (S and T). I'm not sure if there is
a better way.

SELECT S.Div, S.Score, (SELECT Count(*) FROM [qryDistinctDivScore] AS D
WHERE [D].[Score] > [S].[Score])+1 AS SRank, IIf((SELECT Count(*) FROM
[qryDivScore] AS T WHERE T.Score = S.Score )>1,"T","") AS Tie, [SRank] &
[Tie] AS STRank
FROM [qryDivScore] AS S;

b) Your comments re Alias make a lot of sense. (removing ambiguity etc.)


I think I miss counted parenthesis (again), the +1 is clear
to me now.

It's nice that you sorted it out from so few (and
misleading) clues. Way to go!

--
Marsh
MVP [MS Access]
.