Ranking Query Almost Works 2

[TCARPENTER]

I've pouring over everything I can find regarding ranking on scores but ties are still giving me grief. Here's my query:

SELECT quniAAScores.School, quniAAScores.GymnastID, quniAAScores.Gymnast, quniAAScores.Event, quniAAScores.Score, (SELECT 1+ Count(*) FROM quniAAScores AS S WHERE S.Score > quniAAScores.Score AND S.Event = quniAAScores.Event) AS Rank
FROM quniAAScores
WHERE (((quniAAScores.Event)="Bars"))
ORDER BY quniAAScores.Event, quniAAScores.Score DESC;

This is what I'm getting:
[tt][ul]
School GymnastID Gymnast Event Score Rank
[li]20 101 Gymnast1 Bars 8.50 1[/li]
[li]22 133 Gymnast2 Bars 8.50 1[/li]
[li]20 103 Gymnast3 Bars 8.45 3[/li]
[li]18 151 Gymnast4 Bars 8.35 4[/li]
[/ul][/tt]

I've lots of these that have ties and work out meaning 2 isn't skipped - and I would swear this worked before - what am I doing wrong?

BTW, quniScores just normalizes my data, but here's the query FWIW:

SELECT ScoresID, School, GymnastID, Gymnast, "Bars" AS Event, Bars AS Score
FROM Scores
WHERE Bars>0
UNION ALL
SELECT ScoresID, School, GymnastID, Gymnast, "Beam", Beam
FROM Scores
WHERE Beam>0
UNION ALL
SELECT ScoresID, School, GymnastID, Gymnast, "Floor", Floor
FROM Scores
WHERE Floor>0
UNION ALL SELECT ScoresID, School, GymnastID, Gymnast, "Vault", Vault
FROM Scores
WHERE Vault>0;

Thanks for any help.

 

[dhookom]

I used to work in the parks and rec field and the Rank looks good to me. What do you expect to see? 103 shouldn't be ranked 2nd since 2 gymnasts finished ahead of her. 101 and 133 tied for first so their ranking should be 1.

Duane
Hook'D on Access
MS Access MVP

 

[TheAceMan1]

How are ya TCARPENTER . . .

Have a look here How to Rank Records Within a Query

[blue]Your Thoughts? . . .[/blue]

See Ya! . . . . . .

Be sure to see faq219-2884 [blue]Worthy Reading![/blue]
Also faq181-2886 [blue]Worthy Reading![/blue]

 

[TCARPENTER]

Hi Duane,

I need 1 1 2 3, not 1 1 3 4...

Hi TheAceMan1,

I'll dig deeper into the link you provided and see if that one solves the problem for me...

Thanks Guys!

 

[PHV]

What about this ?

SELECT School, GymnastID, Gymnast, Event, Score
, (SELECT 1+ Count(*) FROM (SELECT DISTINCT Score, Event FROM quniAAScores) B WHERE A.Score < B.Score) AS Rank
FROM quniAAScores A
WHERE Event='Bars'

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[dhookom]

Apparently you have chosen to use "Dense" ranking rather than "Standard" ranking. I would have thought Standard would be preferred.

Duane
Hook'D on Access
MS Access MVP

 

[TCARPENTER]

Hi PHV,

That gave me some strange results when I ran it:
[tt][ul]
School GymnastID Gymnast Event Score Rank
[li] 20 101 Gymnast1 Bars 8.50 40[/li]
[li] 22 102 Gymnast2 Bars 8.50 40[/li]
[li] 20 103 Gymnast3 Bars 8.45 44[/li]
[li] 18 151 Gymnast4 Bars 8.35 49[/li]
[/ul][/tt]

So I'm not sure that's it, but it did answer some of the questions regarding TheAceMan1 posted, so I'll keep digging...

 

[TCARPENTER]

Hi Duane,

I'm too much of a newbie to understand the difference between "Dense" vs. "Standard" ranking - I'm sure you're right - I just have no idea what the "nuts and bolts" of that would be.

FWIW, it's really in the reports for the announcer to read the results, the 1 1 3 4, really throws them off - we wind up using pen or pencil to "fix" it before it's read.

 

[dhookom]

Different types of ranking

Duane
Hook'D on Access
MS Access MVP

 

[JonFer]

PHVs query will give you dense ranking if you add a criteria for the Event field in the sub-query that returns the Rank:

(SELECT 1+ Count(*) FROM (SELECT DISTINCT Score, Event FROM quniAAScores) B WHERE A.Score < B.Score [b]And A.Event=B.Event[/b]) AS Rank

 

[TCARPENTER]

That did the trick - thanks to you and PHV, it's now working - thanks and have some stars!