Count Rows By Group and By Specifeid Number

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 By Group and By Specifeid Number

Author

Topic

rekiller
Starting Member

31 Posts

Posted - 2007-11-30 : 18:04:20

I have this


ColumnA    ColumnB
  100        A
  200        A
  300        A
  500        A
  700        B
  900        B
  450        B
  340        B
  110        B
  220        B

I want to make subGroups of 3 rows, group by Column B


SELECT *, ROW_NUMBER OVER(PARTITION BY ColumnB) rowID

It returns me:


ColumnA    ColumnB    RowID
  100        A          1
  200        A          2
  300        A          3
  500        A          4
  700        B          1
  900        B          2
  450        B          3
  340        B          4
  110        B          5
  220        B          6

But, i want subgroups of 3 rows, like this:


ColumnA    ColumnB    RowID
  100        A          1
  200        A          2
  300        A          3
  500        A          1
  700        B          1
  900        B          2
  450        B          3
  340        B          1
  110        B          2
  220        B          3

So, i Want that it resets every 3 rows in one Group

How can you do that?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-11-30 : 18:56:31

[code]rowID = ((ROW_NUMBER() OVER(PARTITION BY ColumnB ORDER BY ColumnA) - 1) % 3) + 1[/code]

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources