CODEWITHSUNDEEP
sqloracle-databasesequence
Juan C. Moreno
Juan C. Moreno

Reputation: 35

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

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?

Upvotes: 0

Views: 68

Answers (1)

Barbaros Özhan
Barbaros Özhan

Reputation: 65105

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)

Upvotes: 1

Related Questions