I have a table like below with versionid (uuid) and productid (int). both are inserted by an application. I would like to add a versionnum int column that will work like an identity column, but per productid:
+-----------+-----------+------------+----+
| versionid | productid | versionnum | id |
+-----------+-----------+------------+----+
| guid...1 | 345 | 1 | 1 |
+-----------+-----------+------------+----+
| guid...2 | 345 | 2 | 2 |
+-----------+-----------+------------+----+
| guid...3 | 345 | 3 | 3 |
+-----------+-----------+------------+----+
| guid...4 | 789 | 1 | 4 |
+-----------+-----------+------------+----+
| guid...5 | 345 | 4 | 5 |
+-----------+-----------+------------+----+
| guid...6 | 789 | 2 | 6 |
+-----------+-----------+------------+----+
| guid...7 | 345 | 5 | 7 |
+-----------+-----------+------------+----+
Is there a lightweight way to insert the versionnum with the other two columns?
The best solution I can think is to add an actual identity column id, at least you'd know productid 789 would have versionnum 4 & 6. But I would like a way for each product to have a sequential id
Thanks