Numbering same values - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Numbering same values

Author

Topic

Mith
Starting Member

2 Posts

Posted - 2009-08-12 : 15:11:42

russell
Pyro-ma-ni-yak

5072 Posts

Posted - 2009-08-12 : 15:24:55

[code]
SELECT col1, row_number() over (partition by col1 order by col1) col2
from yourTable
[/code]

Mith
Starting Member

2 Posts

Posted - 2009-08-13 : 11:58:53

Tnx a lot

I thought there might be some tricky way for solve my problem, instead of playing with loops

quote:

Originally posted by russell


SELECT	col1, row_number() over (partition by col1 order by col1) col2
from	yourTable

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-08-14 : 01:38:32

Hi if u are working in sql2000 then use this


declare @t table (A varchar(32))
insert into @t  select 
'John'   union all select 
'John'   union all select 
'John'   union all select 
'Mich'   union all select 
'Mich'   union all select 
'Terry'  union all select 
'Terry'	union all select
'Terry'	union all select
'Terry'	

select identity(int,1,1)as rid, * into #temp from @t

select a,(select count(a) from #temp where rid <= t.rid and a= t.a)as cnt
from #temp t

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-08-14 : 07:33:22

Also refer point 3
http://sqlblogcasts.com/blogs/madhivanan/archive/2009/06/10/quirky-update-in-sql-server.aspx

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources