alter table t_review add(seq number); update t_review a set seq= ( with tbl as (select id, row_number() over(order by dt, id) rnk from t_review) select rnk from tbl b where b.id=a.id );
Tuesday, December 04, 2018
Generate Serial Number for Existing Table
Table T_REVIEW has column id as the unique key and dt as the timestamp. I want to add a serial number to the table based on the order by dt and id. In the following scripts, I use window function row_number to generate the serial number and update the table.