I have to do something like the following example in SQL with a sequence for the VERSION column:
| ID | VERSION |
|---|---|
| 1 | 1 |
| 1 | 2 |
| 2 | 1 |
| 3 | 1 |
| 3 | 2 |
| 3 | 3 |
| 4 | 1 |
I mean, when a new record is entered with the same ID, the VERSION increases by 1, if it's a different ID it starts at 1 again for that ID, any ideas?
CodePudding user response：
What you need is ROW_NUMBER() Analytic Function such as
SELECT id, ROW_NUMBER() OVER (PARTITION BY id ORDER BY 0) AS version
FROM t
where PARTITION BY performs GROUPING BY, and ORDER BY stands only for being compulsory(so, followed by an arbitrary constant)