CODEWITHSUNDEEP
sqlpostgresql
mcfly
mcfly

Reputation: 1151

Adding Auto Increment Value to Column in relation to Duplicate values in Another Column

I have a large table (3 million rows and about 12 columns). I have one column that can contain duplicate values - this is my "ID" column. I have a second column "NUM_ID" that I would like to have it start at the value of 1 for every unique "ID". Then - if I run into a duplicate value - "NUM_ID" would then bump up one value (to 2) and so on. For example:

ID     NUM_ID
1        1
2        1
2        2
2        3
3        1
3        2
4        1
5        1
5        2
5        3
5        4

Again, "ID" is pre-populated, I cannot change this column and its values. My "NUM_ID" column is currently empty - I'm hoping there is a sql command I can use to populate the column as shown above? I've tried using Python but updating 3M rows is taking a long time. Also, if it matters, I am using PostGresSQL.

Help? Thanks!

Upvotes: 1

Views: 733

Answers (1)

Yogesh Sharma
Yogesh Sharma

Reputation: 50173

If you are Using SQL Server then You Should Use ROW_NUMBER() as below :

SELECT *, ROW_NUMBER() OVER (PARTITION BY ID ORDER BY ID) NUM_ID FROM #TM

Result :

ID  NUM_ID
1   1
2   1
2   2
2   3
3   1
3   2
4   1
5   1
5   2
5   3
5   4

Upvotes: 3

Related Questions