CODEWITHSUNDEEP
sqlsql-serverdatabase
Bryan
Bryan

Reputation: 71

How to generate Row Number Column for each occurrence of an ID in a different column?

I need to count each occurrence of an ID in a column as new column. It should look like this:

enter image description here

I tried it with ROW_NUMBER() OVER (ORDER BY [Id]) as rownum but it did not work.

Can you guy please help me out?

Upvotes: 1

Views: 1610

Answers (1)

Charlieface
Charlieface

Reputation: 71952

In order to reset the numbering for each change of ID, you need a PARTITION BY clause.

There appears to be no specific ordering within the partitions, so you can use ORDER BY (SELECT 1). If you have another column you want to order the numbering by, use that instead.

ROW_NUMBER() OVER (PARTITION BY [Id] ORDER BY (SELECT 1)) as rownum

Upvotes: 3

Related Questions