Post reply

Ranking in non numeric charater

  • April 13, 2017 at 4:26 am

    #342431

    create Table #T
    (i int, j varchar(5))

    Insert into #T
    Values
    (1,'aa'),
    (2,'aa'),
    (3,'bb'),
    (4,'bb'),
    (5,'bb'),
    (6,'cc'),
    (7,'dd'),
    (8,'ee'),
    (9,'ee'),
    (10,'ee')

    --desired output :

    1,'aa',1
    2,'aa',1
    3,'bb',2
    4,'bb',2
    5,'bb',2
    6,'cc',3
    7,'dd',4
    8,'ee',5
    9,'ee',5
    10,'ee',5

    Can we use Rank or Dens_Rank, to obtain the result?

  • April 13, 2017 at 4:35 am

    #1937993

    yuvipoy - Thursday, April 13, 2017 4:26 AM

    Can we use Rank or Dens_Rank, to obtain the result?

    Have you tried? Why don't you and see what happens? 🙂

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
    Larnu.uk

  • April 13, 2017 at 4:56 am

    #1937994

    Select *,Dense_Rank() OVER (PARTITION BY j ORDER BY j asc) AS EventOrder

    from #T
    Select *,Rank() OVER (PARTITION BY j ORDER BY j asc) AS EventOrder
    from #T
    It is not returning desired result, it is just returning 1 for all..

  • April 13, 2017 at 5:08 am

    #1937995

    You're not partitioning by j - you're doing the rank over the whole data set, so just remove the PARTITION BY clause and you'll get the desired result.

    John

Viewing 4 posts - 1 through 3 (of 3 total)

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