| Author |
 Topic  |
|
|
armesjr
Starting Member
9 Posts |
 Posted - 11/25/2009 : 13:00:08
|
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
8513 Posts |
Posted - 11/25/2009 : 13:06:10
|
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. |
 |
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 11/25/2009 : 13:58:05
|
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. |
|
|
|
armesjr
Starting Member
9 Posts |
Posted - 11/25/2009 : 14:10:35
|
| I was trying to move the row 2 over actual row 2. i was just getting ready to try out your response. |
|
|
|
armesjr
Starting Member
9 Posts |
Posted - 11/25/2009 : 14:24:15
|
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. |
|
|
|
webfred
Flowing Fount of Yak Knowledge
Germany
8513 Posts |
Posted - 11/25/2009 : 15:28:43
|
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. |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 11/26/2009 : 02:09:18
|
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 |
|
|
|
armesjr
Starting Member
9 Posts |
Posted - 11/30/2009 : 11:39:29
|
| thanks that worked |
|
|
|
madhivanan
Premature Yak Congratulator
India
22460 Posts |
Posted - 12/01/2009 : 01:38:35
|
quote:
Originally posted by armesjr
thanks that worked
You should also keep my advice
Madhivanan
Failing to plan is Planning to fail |
|
|
| |
Topic |
|
Rank Help
