Count Rows. Increment 1 Every 2 rows By Group

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)

Count Rows. Increment 1 Every 2 rows By Group

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-12-01 : 09:49:26

I need to count some rows, but i need to group them by one column, and increment the counter every 2 rows.

I have This:


ColumA    ColumnB
   A        10
   A        103
   A        104
   A        102
   A        104
   B        101
   B        106
   B        105

I want This:


ColumA    ColumnB    Counter
   A        10         1
   A        103        1
   A        104        2
   A        102        2
   A        104        3
   B        101        1
   B        106        1
   B        105        2

Can you do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-01 : 10:02:27

Didn't I had answer you over this thread http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=93539 ?

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

rekiller
Starting Member

31 Posts

Posted - 2007-12-01 : 10:19:15

No, this is different
The other thread i needed to count 1 2 3 4 and reset the counter
Here, i dont want reset the counter, what i want is increment the counter by one every 2 rows
1 1 2 2 3 3 4 4 5 5 etc

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-12-01 : 10:25:38

Similar technique !

row_no = (row_number() over (partition by ColumnA order by ColumnA, ColumnB) + 1) / 2

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

rekiller
Starting Member

31 Posts

Posted - 2007-12-01 : 11:08:01

Thanks for the 2 solutions

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources