Own id for every unique name in the table?-CodePudding

Is it possible to make a table that has like auto-incrementing id's for every unique name that I make in the table?

For example:

ID   NAME_ID    NAME  
----------------------
1       1       John
2       1       John
3       1       John
4       2       Mary
5       2       Mary
6       3       Sarah
7       4       Lucas

and so on.

CodePudding user response：

First add the column to the table.

ALTER TABLE yourtable
ADD [UID] INT NULL;

``
ALTER TABLE yourtable
ADD constraint fk_yourtable_uid_id foreign key ([UID]) references yourtable([Serial]);

Then you can update the UID with the minimum Serial ID per Name.

UPDATE t
SET [UID] = q.[UID]
FROM yourtable t
JOIN
(
  SELECT Name, MIN([Serial]) AS [UID]
  FROM yourtable
  GROUP BY Name
) q ON q.Name = t.Name
WHERE (t.[UID] IS NULL OR t.[UID] != q.[UID]) -- Repeatability

CodePudding user response：

Use the window function rank() to get a unique id per name. Or dense_rank() to get the same without gaps:

SELECT id, dense_rank() OVER (ORDER BY name) AS name_id, name
FROM   tbl;

I would advise not to write that redundant information to your table. You can generate that number on the fly. Or you shouldn't store name redundantly in that table, name would typically live in another table, with name_id as PRIMARY KEY.

Then you have a "names" table and run "SELECT or INSERT" there to get a unique name_id for every new entry in the main table. See:

Is SELECT or INSERT in a function prone to race conditions?