Adding increment number group by code

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)

Adding increment number group by code

Author

Topic

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-01-29 : 01:44:03

Hi,

I got set data :

Before:-

Code Downline
-----------------
MY001 MYS0002
MY001 MYS0003
MY001 MYS0004
MY002 MYS0054
MY002 MY0026
MY003 MY01922
:
:

After:-

Code Downline Leg
------------------------
MY001 MYS0002 1
MY001 MYS0003 2
MY001 MYS0004 3
MY002 MYS0054 1
MY002 MY0026 2
MY003 MY01922 1
:
:

Any one have soultion for this issue??

Thank you.

Regards,
Micheale

senthil_nagore
Master Smack Fu Yak Hacker

1007 Posts

Posted - 2010-01-29 : 01:52:01

Select Code ,Downline ,row_number() over(partition by Code order by Code) as leg from table_name

Note: work Mssql 2005 or more

Senthil.C
------------------------------------------------------
[Microsoft][ODBC SQL Server Driver]Operation canceled

http://senthilnagore.blogspot.com/

ms65g
Constraint Violating Yak Guru

497 Posts

Posted - 2010-01-29 : 09:20:44

[code]--Declaration
declare @t table (Code varchar(10), Downline varchar(10));

--Data Insertion
insert into @t values ('MY001', 'MYS0002');
insert into @t values ('MY001', 'MYS0003');
insert into @t values ('MY001', 'MYS0004');
insert into @t values ('MY002', 'MYS0054');
insert into @t values ('MY002', 'MY0026');
insert into @t values ('MY003', 'MY01922');

--Standard Ranking: Row_Number with PARTITION

--Correlated Scalar Subquery
select *, rank_no = (select count(*)
from @t as t1
where t1.Code = t2.Code
and t1.Downline <= t2.Downline)
from @t as t2
order by code, downline

--Composite Non Equi Self Join
select t1.Code, t1.Downline, rank_no = count(*)
from @t t1
join @t t2
on t1.Code = t2.Code
and t1.Downline >= t2.Downline
group by t1.Code, t1.Downline
order by t1.Code, t1.Downline

/* RESULT
Code Downline rank_no
---------- ---------- -----------
MY001 MYS0002 1
MY001 MYS0003 2
MY001 MYS0004 3
MY002 MY0026 1
MY002 MYS0054 2
MY003 MY01922 1
*/
[/code]

DP978
Constraint Violating Yak Guru

269 Posts

Posted - 2010-01-29 : 10:27:45

Thank goodness for Row_Number() and Partitions!

micnie_2020
Posting Yak Master

232 Posts

Posted - 2010-02-01 : 21:28:04

Tqvm to ms65g and senthil_nagore
. Both are working fine. Well done!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources