I have table with primary auto increment and i want another column to set unique counter considering other column as primary.
Here is table structure i have,
| primary_id | order_id |
|---|---|
| 567 | 200 |
| 568 | 200 |
| 569 | 200 |
| 570 | 201 |
And i want to add new column like this,
| primary_id | order_id | counter |
|---|---|---|
| 567 | 200 | 1 |
| 568 | 200 | 2 |
| 569 | 200 | 3 |
| 570 | 201 | 1 |
so counter column always have unique counter and should be dependant on order_id.
What should be the best approach in mysql to achieve this?
Counter should always retain unique id even row is removed.
