MySQL: How do you add new id column to the table, group by the old column?

Question

I am trying to add column new_id to the table in MySQL Workbench, but I want this new_id to be GROUP BY the old_id.

I tried the code below. The new_id is automatic increasing, but it is not group by old_id.

ALTER TABLE candidate
ADD COLUMN new_id int not null auto_increment UNIQUE FIRST,
ADD PRIMARY KEY(old_id, new_id);

Below is what I got:

+----------+--------+
| old_id   | new_id |
+----------+--------+
| 00132004 |    1   |
| 00132004 |    2   |
| 00132004 |    3   |
| 00132004 |    4   |
| 00118685 |    5   |
| 00118685 |    6   |
| J99999   |    7   |
| J99999   |    8   |
| J99988   |    9   |
| J99987   |   10   |
+----------+--------+

But this is what I want to get:

+----------+--------+
| old_id   | new_id |
+----------+--------+
| 00132004 |    1   |
| 00132004 |    1   |
| 00132004 |    1   |
| 00132004 |    1   |
| 00118685 |    2   |
| 00118685 |    2   |
| J99999   |    3   |
| J99999   |    3   |
| J99988   |    4   |
| J99987   |    5   |
+----------+--------+

What am I missing here....? Thank you!!!

Answer 1

Your new requirement for new_id will not work making that column auto increment, because then the values will not be unique or incremental.

What you are looking for is something called the dense rank. MySQL does not have built in support for this, but you can simulate it using session variables:

SET @dense_rank = 0;
SET @old_id = NULL;

SELECT
    @dense_rank:=CASE WHEN @old_id = old_id
                      THEN @dense_rank ELSE @dense_rank + 1 END AS dr,
    @old_id:=old_id AS old_id,
    new_id
FROM candidate
ORDER BY new_id

Note that because MySQL does not support any clean way of automatically having a dense rank maintained, a SELECT query might be your best long term option. This way, you can just compute the dense rank from the latest data whenever you need it, without needing to worry about maintaining it in your actual table.

Output:

Demo here:

Rextester