1

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.

4
  • Should order be the number in name, or the relative position among names? I.e. for name S1, S2, S5, would order be 1,2,3 or 1,2,5? Jan 23, 2019 at 8:11
  • Yes, an order should relative to name. If S1 - order 1, if S2 - order 2
    – Bob
    Jan 23, 2019 at 8:12
  • In my example, S1, S2, S5, what would order be? Jan 23, 2019 at 8:13
  • Should be 1, 2 and 5
    – Bob
    Jan 23, 2019 at 8:15

2 Answers 2

2

You can use ROW_NUMBER() window function:

WITH x AS
(
  SELECT
      id,
      name,
      ROW_NUMBER() OVER (ORDER BY name) ord
  FROM
      tbl
)
UPDATE
    tbl
SET
    ord = x.ord
FROM
    x
WHERE
    x.id = tbl.id;
SELECT * FROM tbl;
id | name | ord
-: | :--- | --:
 1 | S4   |   4
 2 | S2   |   2
 3 | S3   |   3
 4 | S1   |   1

db<>fiddle here

If there isn't duplicated names you can remove 'S' from name:

UPDATE
    tbl
SET
    position = right(name, length(name) - 1)::int

db<>fiddle here

11
  • Position can not be same. How to avoid adding records with same position?
    – Bob
    Jan 23, 2019 at 7:59
  • Please update your question by adding a real example of your data and the expected result.
    – McNets
    Jan 23, 2019 at 8:00
  • 1
    @Bob, row_number() will never repeat the same number per partition (there's only one partition here) Jan 23, 2019 at 8:04
  • I added real state and expected one. Your select doesn't update my table. I need to use sequence I guess to update orders number and avoid adding another record with same position.
    – Bob
    Jan 23, 2019 at 8:04
  • @McNets I had an error ERROR: syntax error in or close "FROM" LINE 7: FROM ^ SQL state: 42601 Character: 92 I change table name.
    – Bob
    Jan 23, 2019 at 8:11
0

If all you want is everything after the letter S, something like:

update tbl 
    set ord = substr(name,2)::int;

should do. Question is why you have to have a column for this, can't you just order by:

select * from tbl order by substr(name,2)::int;

It is good practice to not duplicate data unless there's a good reason for it.

1
  • I have simple names now but it is going to change in the future so I need position column to show what was first, second, third etc..
    – Bob
    Jan 23, 2019 at 8:38

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.