Apply a sequence based on the value of another column-CodePudding

I would like to create a sequence that starts with 1000 and increments by one unit. Then, apply this sequence to the variable "identification" of my table "person". The number of records in the table "person" is 958, so it should increment to the end by default. In addition, I want the numbering to be based on the Age field sorted in descending order. It's to say: the field 'Identification' has no records, it has NULL values. When I say sort by age, I mean that the one with the youngest age will be assigned the ID number 1000, the second youngest will be assigned 1001 and so on.

I have tried to do something similar to the following but I get no results. I have also tried to put an order by age desc in the middle of the sentence also without result. Any idea to do it only using sequences please?


TABLE PERSON (Name, Surname, City, Identification, Age)

CREATE SEQUENCE seq 
START WITH 1000
INCREMENT BY 1
MINVALUE 1000;

ALTER TABLE person
ADD COLUMN identification integer DEFAULT nextval('seq');

CodePudding user response：

A quick example using dummy data:

create table age_seq_test(age int , fld_1 varchar, id integer);

insert into age_seq_test values (10, 'test'), (30, 'test2'), (20, 'test3');

select * from age_seq_test order by age;
 age | fld_1 |  id  
----- ------- ------
  10 | test  | NULL
  30 | test2 | NULL
  20 | test3 | NULL

BEGIN;

with t as 
   (select age, row_number() over (order by age) as rn from age_seq_test) 
update 
    age_seq_test AS ast 
set 
    id = t.rn   999
from 
    t 
where  
    ast.age = t.age  ;

select * from age_seq_test order by age;
 age | fld_1 |  id  
----- ------- ------
  10 | test  | 1000
  20 | test3 | 1001
  30 | test2 | 1002


--COMMIT/ROLLBACK depending on what the SELECT shows.