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.