Add an Order column to a table that contains data with SQL query-CodePudding

Recently I was working on a project that needs to add a column to sort existing records. To clarify the subject, I will give a small example. Suppose we have a table to store the names of Corona vaccine injection centers and another table to register the people who come to each of these centers. After collecting the data, we come to the conclusion that to manage the order of turns, we must add a "Order" column to Person table. How can I do this by writing a query without losing the data collected so far?

Here is the table of centers:

And this is the table of person:

And what I expect is something like this:

PS: I use SQL to create tables and store data.

CodePudding user response：

I would create a view for this with the Order column created on the fly.

Is it an order or a sequence? I'd avoid "order" as it's a reserved word.

Create view PersonSequence as

Select Id, Name, CentreId,
  row_number() over (partition by CentreId order by Id) as Sequence
from Person

CodePudding user response：

select Id,
       Name,
       CenterID,ROW_NUMBER() over (partition by CenterId order by Id) as Order
from person