CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
vantian
vantian

Reputation: 888

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

Upvotes: 0

Views: 100

Answers (2)

vittore
vittore

Reputation: 17579

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

Upvotes: 3

Eric Hauenstein
Eric Hauenstein

Reputation: 2565

If it's always -4, then:

Select (number-4), name
from table

But I doubt it's that simple.

Upvotes: 0

Related Questions