SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 Script Library
 Rank Help
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

armesjr
Starting Member

9 Posts

Posted - 11/25/2009 :  13:00:08  Show Profile  Reply with Quote
I have a table with a lot of columns. For each distinct ID, there are multiple entries. Instead of having these long numbers in Row 2, I just want it to be 1,2,3,etc depending on how many there are

Distinct ID row 2
090120E5190700 5668655024
090120E5190700 5668655025

090120E5191300 5657004675
090120E5191300 5657004676
090120E5191300 5657004677
090120E5191300 5657004678

Want it to look like

Distinct ID row 2
090120E5190700 1
090120E5190700 2

090120E5191300 1
090120E5191300 2
090120E5191300 3
090120E5191300 4

Edited by - armesjr on 11/25/2009 13:39:28

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/25/2009 :  13:06:10  Show Profile  Visit webfred's Homepage  Reply with Quote
SQL Server 2005 and later versions:
select
ID,
ROW_NUMBER() over (partition by ID order by ID) as rownum
from your_table


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/25/2009 :  13:58:05  Show Profile  Visit webfred's Homepage  Reply with Quote
Funny!
You have done an edit to your post without giving any reply to my solution?


No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

armesjr
Starting Member

9 Posts

Posted - 11/25/2009 :  14:10:35  Show Profile  Reply with Quote
I was trying to move the row 2 over actual row 2. i was just getting ready to try out your response.
Go to Top of Page

armesjr
Starting Member

9 Posts

Posted - 11/25/2009 :  14:24:15  Show Profile  Reply with Quote
webfred that query worked for assigning the row number. thank you.

however, what i want to do. i dont think i really made myself clear, is update my table so that row 2, has these numbers, or there is another column with the row numbers.
Go to Top of Page

webfred
Flowing Fount of Yak Knowledge

Germany
8765 Posts

Posted - 11/25/2009 :  15:28:43  Show Profile  Visit webfred's Homepage  Reply with Quote
Maybe you will need that old values at a later time so I think it is good to have new column.
So here we go:

alter table your_table add rownum int
go
update t1
set rownum = dt.rownum
from your_table t1
join 
(select 
ID,
[row 2] as row_2,
ROW_NUMBER() over (partition by ID order by ID) as rownum
from your_table)dt
on dt.ID = t1.ID and dt.row_2 = t1.[row 2]



No, you're never too old to Yak'n'Roll if you're too young to die.
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 11/26/2009 :  02:09:18  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
Updating the table is not a good idea. You should keep on updating the table as soon as new data are added. Better use it in SELECT statement only. If you use front end application, you can do this numbering there

Madhivanan

Failing to plan is Planning to fail

Edited by - madhivanan on 11/26/2009 02:09:54
Go to Top of Page

armesjr
Starting Member

9 Posts

Posted - 11/30/2009 :  11:39:29  Show Profile  Reply with Quote
thanks that worked
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22755 Posts

Posted - 12/01/2009 :  01:38:35  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by armesjr

thanks that worked


You should also keep my advice

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.09 seconds. Powered By: Snitz Forums 2000