SQL help needed urgent ... - 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)

SQL help needed urgent ...

Author

Topic

frank.svs
Constraint Violating Yak Guru

368 Posts

Posted - 2007-11-20 : 15:27:52

Hi pals,

I need small help.
I have a table with data as follows.

select * from test_data

c1 c2 c3
----------------------
111 4 101
122 3 101
133 2 102
144 2 103
155 1 103

Now i need to generate a new column c4 as sequence number in the output

I need to get the output something as follows

c4 c1 c2 c3
-----------------------------
1 111 4 101
2 122 3 101
1 133 2 102
1 144 2 103
2 155 1 103

The newly generated column contains sequence numbers starting from 1 and the sequence should be resetted again to 1 whenever a new c3 value is encountered(for example when the c3 value changes from 101 to 102 the sequence should be resetted to 1).

Can anyone help me out on this regard.

Thanks in advance,
franky

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-20 : 18:27:05

try this...


declare @table table (c1 int, c2 int, c3 int)
insert into @table
	select 111, 4, 101 union
	select 122, 3, 101 union
	select 133, 2, 102 union
	select 144, 2, 103 union
	select 155, 1, 103


select	row_number() over (partition by c3 order by c3) as [c4],  
        c1,
        c2,
        c3
from	@table

Nathan Skerl

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-20 : 21:06:37

You might want to order by c1 instead of c3

row_number() over (partition by c3 order by c1) as [c4],

KH
[spoiler]Time is always against us[/spoiler]

nathans
Aged Yak Warrior

938 Posts

Posted - 2007-11-20 : 21:12:33

yes, good catch

Nathan Skerl

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources