Post reply

RowNumber not sorted by RowNumber

  • June 15, 2010 at 11:15 am

    #233747

    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

    #1180601

    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

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply