I have data that looks like this:



EntityScoreTable
ENTITY YEAR SCORE
entityA 2005 90
entityB 2005 95
entityC 2005 92

entityA 2006 97
entityB 2006 91
entityC 2006 93

I am trying to create a query that returns for each Entity its rank in each year. Something like this:


ENTITY YEAR RANK
entityA 2005 3
entityB 2005 1
entityC 2005 2

entityA 2006 1
entityB 2006 3
entityC 2006 2

My query has an alias for the entity. The columns in the design grid are Entity, Score, and this expression:

Rank: (Select Count(*) from [EntityScoreTable] Where [Score*-1 < ([EntityAlias].[Score] *-1) +1 )

The -1 creates a ranking from high to low.

This works to produce rankings across all years. But . .

I am stumped as to how to produce a ranking based on each year.

Any help would be appreciated.