Sql Server give ID's

I have the first two columns in a table.  i want to create a third column just like I have to give it an ID.  Notice that depending on if the value of the second column repeats itself for the same value in column one, i want to increment by 1, like i have shown you in the grid here. So essentially i want to create the 3rd column (ID) in code.

Just can't figure out how to do this in code.  Can someone help.

Column 1      Column 2      ID
46      23      1
46      26      1
46      27      1
46      23      2
47      27      1
47      24      1
49      24      1
50      24      1
50      24      2
50      27      1
joygomezAsked:
Who is Participating?
 
HainKurtSr. System AnalystCommented:
if you dont have any other column, we may need additional step to properly find & sort the record....

Step1:

create a new table: tmp(id - identity column, col1,col2)

then

do these






set identity_insert Intuit_Staging_Invoice on;

insert into tmp(col1,col2) select * from Intuit_Staging_Invoice;

delete from Intuit_Staging_Invoice;

set identity_insert Intuit_Staging_Invoice off;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by id) from tmp;

Open in new window

0
 
HainKurtSr. System AnalystCommented:
this query will give you third column

select col1, col2, row_number() over (partition by col1, col2 order by ???) rn
from myTable

then join this with original and update your col3 (you may not need this step since you can always get it with query above)


0
 
HainKurtSr. System AnalystCommented:
to update it

update t
set t.col3=x.rn
from test3 t inner join
(select col1, col2, sort, row_number() over (partition by col1, col2 order by [sort]) rn from test3) x
on t.col1=x.col1 and t.col2=x.col2 and t.[sort]=x.[sort]
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
joygomezAuthor Commented:
HainKurt,
Can you be a bit more specific with the syntax.  I am quite new to sql...:(  what is the ??? and rn for.
Thanks a lot
0
 
joygomezAuthor Commented:
ok, i am getting errors... can you make this dummy proof for me please.  

this is my table - Intuit_Staging_Invoice
this is the first column in the above table - IntuitQboInvoiceId
this is the second column in the above table - IntuitQboItemId
the third column is what needs to be updated  - InvoiceItemInternalId
0
 
joygomezAuthor Commented:
what is test3
0
 
HainKurtSr. System AnalystCommented:
here it is

update t
set t.InvoiceItemInternalId=x.rn
from Intuit_Staging_Invoice t inner join
(select IntuitQboInvoiceId, IntuitQboItemId, sort, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by [sort]) rn from Intuit_Staging_Invoice) x
on t.IntuitQboInvoiceId=x.IntuitQboInvoiceId and t.IntuitQboItemId=x.IntuitQboItemId and t.[sort]=x.[sort]

we need another column in your table properly sort and find the record, I used [sort] column... are these the only 3 columns in your table? no other column? like create date or some other column?
0
 
HainKurtSr. System AnalystCommented:
oops, sorry, identity insert should be turned on tmp
set identity_insert tmp on;

insert into tmp(col1,col2) select * from Intuit_Staging_Invoice;

set identity_insert tmp off;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by id) from tmp;

Open in new window

0
 
HainKurtSr. System AnalystCommented:
we can do it without and identity column on tmp

create a new table: tmp (IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)


insert into tmp select *, row_number() over (order by 1) from Intuit_Staging_Invoice;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window

0
 
joygomezAuthor Commented:
Thanks,
got it to work....
0
 
HainKurtSr. System AnalystCommented:
or we can use select into to create table automatically & insert in one shot



select * into tmp
from (
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by 1) from Intuit_Staging_Invoice
) x;

delete from Intuit_Staging_Invoice;

insert into Intuit_Staging_Invoice(IntuitQboInvoiceId, IntuitQboItemId, InvoiceItemInternalId)
select IntuitQboInvoiceId, IntuitQboItemId, row_number() over (partition by IntuitQboInvoiceId, IntuitQboItemId order by InvoiceItemInternalId) from tmp;

Open in new window

0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.