June 15, 2010 at 11:15 am
I'm pulling a dataset (like grades in a class), filtering (let's say males only) and sorting it (by name), so that I have results like this ...
Name, Score
Andrew, 80
Charles, 72
Eric, 90
Harold, 95
Larry, 55
Oliver, 66
What I want is to continue to sort by Name, but assign a 'rank' to the scores, like this ...
Name, Score, Rank
Andrew, 80, 3
Charles, 72, 4
Eric, 90, 2
Harold, 95, 1
Larry, 55, 6
Oliver, 66, 5
Is there a way to get reporting services to do this? I'd like to avoid pushing the filter up to a parameter in the source query.
June 15, 2010 at 11:22 am
You should be able to use Row_number. Since you haven't posted any table definitions, this is rough and untested.
SELECT name, mark, Row_number() Over (Order By Mark DESC) as Rank
FROM StudentMarks
Order By Name
If you want identical marks to get identical ranks, instead of row_number use RANK or DENSE_RANK. Same kinda thing applies.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply