Renumber dynamic column without update in SQL Server

I have this data

5 | Batman
5 | Superman
5 | Wonderwomen
6 | Green Lantern
6 | Green Arrow
7 | Cyborg

when I do select query, I want renumber to

1 | Batman
1 | Superman
1 | Wonderwomen
2 | Green Lantern
2 | Green Arrow
3 | Cyborg

thought?

EDIT:

thanks to vittore, so i came up with this solution. I'm not sure if my query is good. I do ROW_NUMBER() twice. In case my sequence Id is jumping, this query will renumbering perfectly.

WITH cte AS
(
SELECT ROW_NUMBER() OVER(PARTITION BY id ORDER BY id asc) AS CteId
FROM MyTable
)
 SELECT
 ROW_NUMBER() OVER(PARTITION BY CteId ORDER BY CteId asc) AS RenumberColumn
 FROM cte

Answers


RANK function is what you are looking for

select RANK() OVER (ORDER BY id), name
from t

Check row_number() and dense_rank() when you reading about it as well.

UPDATE: If you just use rank alone, it will give you not the values you want ( 1 1 1 2 2 3 ), but ranked values ( 1 1 1 4 4 6 )

So in order to get (1 2 3) group, rank and join:

  select a.r, t.name from t  
      inner join (select id, rank() over (order by id asc) r 
                  from t group by id) a 
         on t.id = a.id

Need Your Help

spring-core dependency version error with a Jenkins Plugin

spring maven jenkins dependencies jenkins-plugins

I'm trying to make a Jenkins plugin that uses a library that requires spring-core 3.2.2 (cloudfoundry-client-lib). I simply used the mvn command to create a skeleton plugin, then added my Maven

Why won't the Tooltip appear correctly when i hover the mouse over the Kendosparkline?

kendo-ui kendo-grid sparklines

I created Kendosparkline chart in a grid , I created two sparklines over each other, but when i hover the mouse over the sparkline , the tooltip won't appear correctly

About UNIX Resources Network

Original, collect and organize Developers related documents, information and materials, contains jQuery, Html, CSS, MySQL, .NET, ASP.NET, SQL, objective-c, iPhone, Ruby on Rails, C, SQL Server, Ruby, Arrays, Regex, ASP.NET MVC, WPF, XML, Ajax, DataBase, and so on.