SQLTeam.com | Weblogs | Forums

Temp Table - Identity that resets unless a match appears


#1

I am trying to figure out how to do the following.
I have a set of IDs and whenever it is unique, I want it to say 1...if there are multiple IDs, it should count 1,2. It will always reset to 1. The reason for doing this is that in SSRS, I cannot sum values that are grouped.
Please see example below

IDs Row Counter that
98765 1
98766 1
98766 2
98767 1
98768 1
98768 2
98769 1
98770 1

Thanks


#2

Look at SELECT IDs, ROW_NUMBER() OVER (PARTITION BY IDs) AS Cnt FROM MyTable