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

Question

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!

FWIW, if you're using postgresql, you probably should remove mysql and sql-server from your tag list. — Xedni
– Xedni, Commented Oct 4, 2017 at 4:29
Rather than adding a column to the table here, I think you would be better with a view that uses ROW_NUMBER() OVER (PARTITION BY ID ORDER BY whatever) for NUM_ID. — ZLK
– ZLK, Commented Oct 4, 2017 at 4:32

Yogesh Sharma · Accepted Answer · 2017-10-04 04:34:09Z

3

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 :

answered Oct 4, 2017 at 4:34

Yogesh Sharma

50.2k5 gold badges31 silver badges53 bronze badges

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

4 Comments

mcfly Over a year ago

How do I write this to the actual column "NUM_ID"?

Yogesh Sharma Over a year ago

i think you don't need to do this.

mcfly Over a year ago

I would like to convert the two columns into a Primary Key (ID,NUM_ID) - which is why I'm hoping to populate the column.

mcfly Over a year ago

Just in case anyone is curious - I solved this by creating a new table and then just copying the column. Thanks @Yogesh Sharma

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related