I would like to add an order column wich will be position of my record. Positions are connected with name. If the name is S1 - order should be 1, for S2 - order should equal 2 etc. How can I do this?
Real state:
id name position
------- ------- -------
1 S4 null
2 S2 null
3 S3 null
4 S1 null
Expected:
id name position
------- ------- -------
1 S4 4
2 S2 2
3 S3 3
4 S1 1
I tried with update:
WITH x AS
(
SELECT
id,
name,
ROW_NUMBER() OVER (ORDER BY name) position
FROM
stage
)
UPDATE
stage
SET
position = x.position
FROM
x
WHERE
x.id = stage.id;
Stage is the name of the table.