CODEWITHSUNDEEP
sql-server-2008auto-increment
Bob Sponge
Bob Sponge

Reputation: 4738

SQL Server : multiple autoincrement on one column

There is a table as follow:

Id   ||  ParentId ||  Other Columns ... 
=======================================
1    ||  1        || ...
2    ||  1        || ...
3    ||  1        || ...
1    ||  2        || ...
2    ||  2        || ...
3    ||  2        || ...
1    ||  3        || ...
2    ||  3        || ...

Column [Id] must have autoincremented value based on [ParentId] (own numeration).

What is the best way to achieve this goal?

Upvotes: 0

Views: 1653

Answers (2)

Joe G Joseph
Joe G Joseph

Reputation: 24046

try this

select ROW_NUMBER() over(partition by parentId order by <any other column>) ID,
ParentId,<other columns>  
from  yourtable

edit1:

If you want the id to be used in the where clause

with cte as(                                
select ROW_NUMBER() over(partition by parentId order by <any other column>) ID,
ParentId,<other columns>  
from  yourtable)
select * from cte where ID=(some value)

Upvotes: 2

Satinder singh
Satinder singh

Reputation: 10198

Demo

http://sqlfiddle.com/#!3/32645/6

Updated http://sqlfiddle.com/#!3/32645/13

create table dummy(parentId int)

insert into dummy values(1)
insert into dummy values(1)
insert into dummy values(2)
insert into dummy values(1)
insert into dummy values(4)
insert into dummy values(4)
insert into dummy values(5)
insert into dummy values(1)

select row_number() over (order by parentID) as ID,parentID from dummy

Upvotes: 0

Related Questions