Results 1 to 4 of 4
  1. #1
    Join Date
    Sep 2009
    Posts
    4

    Unanswered: Help with RANK please

    I have a table as follows:

    Name marks
    ----------------------------
    A 90
    B 91
    C 88
    D 88
    E 87
    F 95

    I would like to rank them as follows:

    Name marks Rank
    ----------------------------------
    A 90 3
    B 91 2
    C 88 4
    D 88 4
    E 87 6

    F 95 1

    The issue here is - if there is a tie ( 88 in our case) the immediate rank should have same number of gaps in the rank ( in this case rank for 87 is 6 not 5, if there was three 88 the immediate rank would be 7).

    Can some one help me to achieve this please.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    which version of sql server, please?

    because rank is really easy with analytic functions in sql2005
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Sep 2009
    Posts
    4
    I am using sql server 2005

  4. #4
    Join Date
    Sep 2009
    Posts
    4
    Hey,

    I could able to solve it. Its really simple. Thanks for the clue.

    declare @s table (nm varchar(10), marks decimal(5,2))
    insert into @s values('A', 91)
    insert into @s values('B', 90)
    insert into @s values('C', 88)
    insert into @s values('D', 88)
    insert into @s values('E', 76)
    insert into @s values('F', 75)

    SELECT RANK() OVER (ORDER BY marks desc) position,
    nm,
    marks
    FROM @s

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •