1

I am trying to figure out how to do an Auto-increment based on change in values in a different column as shown below

This is what I am getting right now

OtherID | AUTOINCREMENT
--------+---------------
A       | 1
A       | 2 
B       | 3
C       | 4
D       | 5
D       | 6

This is what I am hoping for

OtherID | AUTOINCREMENT
--------+---------------
A       | 1
A       | 1 
B       | 2
C       | 3
D       | 4
D       | 4
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Rbird
  • 13
  • 4

2 Answers2

0

This would be a windows function in SQL Server

select
   OtherID,
   row_number() over (partition by OtherID order by OtherID) as AutoIncrement
from
   SomeTable

The order by OtherID works here assuming your OtherID is what you state it is... a character starting with A and moving up the alphabet. Otherwise, you can replace it with (select null)

select
   OtherID,
   row_number() over (partition by OtherID order by (select null)) as AutoIncrement
from
   SomeTable

EDIT

select
   OtherID,
   dense_rank() over (order by cast(left(OtherID,len(OtherID) - 1) as int)) as AutoIncrement
from
   SomeTable
S3S
  • 24,809
  • 5
  • 26
  • 45
  • Thanks for replying. I tried your code with the (select null) since 'otherId' column is not alphabet, it is still not working properly. Here are the exact values for otherId column. It starts with 1H,2H,2H,3H....100H,101H. So in this case I would like the incremental value to be 1,2,2,3...etc – Rbird Aug 18 '17 at 19:16
  • The value would still be incremented based on the partition but the order of this increment is hard because you are mixing numeric and character data – S3S Aug 18 '17 at 19:18
  • @Rbird see the edit based off your new information. This is why accurate test data is so important... – S3S Aug 18 '17 at 19:45
0

try this using row_number()

select Row_number() over(partition by OtherID  order by
 (select 1)),AUTOINCREMENT from mytable
Dgan
  • 10,077
  • 1
  • 29
  • 51