Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Sql Server give ID's

Posted on 2011-09-27
11
Medium Priority
?
201 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:joygomez
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 4
11 Comments
 
LVL 59

Expert Comment

by:HainKurt
ID: 36713999
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
 
LVL 59

Expert Comment

by:HainKurt
ID: 36714029
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
 

Author Comment

by:joygomez
ID: 36714038
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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 

Author Comment

by:joygomez
ID: 36714065
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
 

Author Comment

by:joygomez
ID: 36714086
what is test3
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36714148
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
 
LVL 59

Accepted Solution

by:
HainKurt earned 2000 total points
ID: 36714171
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
 
LVL 59

Expert Comment

by:HainKurt
ID: 36714174
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
 
LVL 59

Expert Comment

by:HainKurt
ID: 36714180
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
 

Author Closing Comment

by:joygomez
ID: 36714184
Thanks,
got it to work....
0
 
LVL 59

Expert Comment

by:HainKurt
ID: 36714188
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

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question