Rank Help - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Script Library

Rank Help

Author

Topic

armesjr
Starting Member

9 Posts

Posted - 2009-11-25 : 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

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-25 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-25 : 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 - 2009-11-25 : 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 - 2009-11-25 : 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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-11-25 : 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

22864 Posts

Posted - 2009-11-26 : 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

armesjr
Starting Member

9 Posts

Posted - 2009-11-30 : 11:39:29

thanks that worked

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-12-01 : 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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources