How do I create a sql sequence based on a specific column?

Question

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?

Barbaros Özhan · Accepted Answer · 2022-05-31 22:21:56Z

1

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)

answered May 31, 2022 at 22:21

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How do I create a sql sequence based on a specific column?

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related