SQL IDENTITY column based on another column

Question

Is there a way to define an identity column on another column? What I want to accomplish is a table that holds positions of an order and these orders can be put there anytime. So it could be that there are already lets say three positions in the table and it would look somewhat like this:

OrderNumber | OrderPosition
10001         1
10001         2
10001         3

And now I want to add another position without calculating the right value for the OrderPosition column. This is because I want to write new positions for multiple orders into the table and would like to avoid cursoring over the individual orders. I would prefer a solution wher OrderPosition is an identity column that is reseeded based on the OrderNumber column. So that If i add an order position for a new order it would start with 1 and if I add another position for order 10001 it would continue with 4.

Hi could you please post your solution? Either as an edit or a separate answer? — narain, Aug 10 '16 at 20:11

score 1 · Answer 1 · answered Feb 27 '15 at 15:25

1

Write a Scalar Function that returns the MAX(OrderPosition) based on OrderNumber. Then reference that function in the insert statement of orders

answered Feb 27 '15 at 15:25

Dbloch

2,326
1
13
15

Wouldn't that result in an error if I insert two new positions for the same order since the insert of the first row wouldn't be committed and the function would return the same result for both rows? – Romano Zumbé Feb 27 '15 at 15:28
Short answer is yes, it is possible. What is the likley hood: That would depend on if the two inserts were within the same transaction or not. Also the amount of orders being processed in regards to accessing the same order at the same time. – Dbloch Feb 27 '15 at 15:31
Unfortunately it is more than possible that I will insert multiple positions for one order in a single batch. – Romano Zumbé Feb 27 '15 at 15:33
I think a solution would be to add a row_number to the returnvalue of the function. I'll give it a try. – Romano Zumbé Feb 27 '15 at 15:37
That did the trick. Would you like to update your answer to include the row_number? I'll mark it as correct then. Thank you – Romano Zumbé Feb 27 '15 at 15:40

score 0 · Answer 2 · answered Feb 27 '15 at 15:59

your requirement will not work for identity column.

You need to create custom logic to get from the normal columns and on combination based new no will generate.. like (read comments, only choose one logic)

declare @t table(OrderNumber  int, OrderPosition int)

insert into @t values (10001, 1),(10001, 2),(10001, 3),(10001, 4)

select * from @t

--now insert new record with old orderno
declare @seq int = 1
declare @ordernumberNew int = 10001 

--Eigher you can use :- insert to more understand
if( exists(select orderposition from @t where OrderNumber = @ordernumberNew ))
 begin
    set @seq = (select max(OrderPosition) + 1 from @t where OrderNumber = @ordernumberNew )
 end

insert into @t values (@ordernumberNew , @seq )

select * from @t

--or another twist of above statement, insert directly as
    insert into @t
    values
    (
        @ordernumberNew,
        case when exists (select orderposition from @t where OrderNumber = @ordernumberNew )
        then (select max(OrderPosition) + 1  from @t where OrderNumber = @ordernumberNew )
        else 1 end
    )

select * from @t    

--Now enter the not exist order no
set @ordernumberNew = 10006

    insert into @t
    values
    (
        @ordernumberNew,
        case when exists (select orderposition from @t where OrderNumber = @ordernumberNew )
        then (select max(OrderPosition) + 1  from @t where OrderNumber = @ordernumberNew )
        else 1 end
    )

select * from @t

But these are all iterative logics, aren't they? I would have to iterate over all order numbers. I need to avoid that. Or do I miss something here? — Romano Zumbé, Feb 27 '15 at 16:10
"iterative over all order numbers " mean.. In your question you written as "So that If i add an order position", My answer is based on this.. what you need more... are you want insert or update the record..explain with example... — Ajay2707, Feb 27 '15 at 16:22
I also wrote "This is because I want to write new positions for multiple orders into the table and would like to avoid cursoring over the individual orders.". I think the first answer that was given already solves my problem. Thank you anyway. — Romano Zumbé, Feb 27 '15 at 16:24
Dear the same thing I written in direct query... which give the max value based on current insert order no...case statement.. I am not clearly understand your english, pl. you tell me what you want update seq. in insert or update the existing table. — Ajay2707, Feb 27 '15 at 16:30

SQL IDENTITY column based on another column

2 Answers2