I have a table like this:
id | value |
---|---|
15 | a |
15 | b |
18 | c |
19 | d |
20 | e |
20 | f |
I want to create a select query with replaces the id value with integers starting from 1.
id | value |
---|---|
1 | a |
1 | b |
2 | c |
3 | d |
4 | e |
4 | f |
This is my expectation. I am using SQL Server 2019.
CodePudding user response:
We can try an updateable CTE with the help of DENSE_RANK
:
WITH cte AS (
SELECT *, DENSE_RANK() OVER (ORDER BY id) dr
FROM yourTable
)
UPDATE cte
SET id = dr;