CODEWITHSUNDEEP
sqlcount
Abe
Abe

Reputation: 1929

Count occurence of a value in a column and add to new column in SQL 2008 R2

I have a table like so

DBName         RunID          SegNo
FM1            42              1
FM2            47              2
FM2            47              3

I would like the number of time the RunID is occured in a new column. The result will be as below

DBName         RunID          SegNo         Position
FM1            42              1              1  
FM2            47              2              1
FM2            47              3              2

Thanks for your help.

Upvotes: 0

Views: 36

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460098

You can use ROW_NUMBER:

SELECT DBName, RunID, SegNo,
       Position = ROW_NUMBER() OVER (PARTITION BY RunID ORDER BY DBName)
FROM dbo.Table1

Demo

Upvotes: 1

Related Questions