partition rows based on identity key

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)

partition rows based on identity key

Author

Topic

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-09 : 13:24:00

Hi:
Does anyone have a solution to this problem?

How can I provide a partition of rows in a table from 1 to 5 table based upon the identity key.
So if I have a table as such:

create table TAB
ID identity (1,1)
countnum int

and the values are
ID countnum
1 0
1 0
1 0
1 0
1 0
1 0
1 0
1 0
2 0
2 0
3 0
5 0
5 0

I can change the countnum field as follows

1 1
1 2
1 3
1 4
1 5
1 1
1 2
1 3

2 1
2 2

3 1

5 1
5 2

This is not based upon another value as one could do with ranking by a value in a field. Its just partioning the rows into groups of 5 and the leftover remainder count (if rows of same key are not divisable by 5) by the identity key.

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 15:45:21

you can do it with row_number()

select case when rownbr > 5 then rownbr - 5 else rownbr end as nbr, *
from (
select row_number() over(partition by id order by id) as rownbr, id
from [YourTable] ) t

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-09 : 16:39:34

Well, this is clearly the right track but I must have missed something because the results were as follows

if the number of identical ID's was under 10, the results in nbr were correct. But in the case of 12 identical ID's the results in nbr were:
1,2,3,4,5,1,2,3,4,5,6,7 instead of 1,2 I had 6,7.

But it worked if there were 7 identical *ID,s which was for nbr:
1,2,3,4,5,1,2

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 16:47:35

quote:
Originally posted by smh

Well, this is clearly the right track but I must have missed something because the results were as follows

if the number of identical ID's was under 10, the results in nbr were correct. But in the case of 12 identical ID's the results in nbr were:
1,2,3,4,5,1,2,3,4,5,6,7 instead of 1,2 I had 6,7.

But it worked if there were 7 identical *ID,s which was for nbr:
1,2,3,4,5,1,2

So you want the results grouped by fives?

select case cast(right(cast(rownbr as varchar), 1) as int)
when 6 then 1 when 7 then 2 when 8 then 3
when 9 then 4 when 0 then 5 else cast(right(cast(rownbr as varchar), 1) as int) end as nbr, *
from (
select row_number() over(partition by id order by id) as rownbr, id
from [YourTable] ) t

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 16:54:06

or perhaps something like this: "nbr % 5"


declare @t table (ID int, countnum int)
insert @t (ID, countnum)
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 2, 0 union all
select 2, 0 union all
select 3, 0 union all
select 5, 0 union all
select 5, 0

;with cte (id, rn) as
(
select id, row_number() over (partition by id order by id)
from	@t
)
select	id
	,isNull(nullif(rn % 5, 0),5)
from	cte

Be One with the Optimizer
TG

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 17:05:33

quote:

Originally posted by TG

or perhaps something like this: "nbr % 5"


declare @t table (ID int, countnum int)
insert @t (ID, countnum)
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 1, 0 union all
select 2, 0 union all
select 2, 0 union all
select 3, 0 union all
select 5, 0 union all
select 5, 0

;with cte (id, rn) as
(
select id, row_number() over (partition by id order by id)
from	@t
)
select	id
	,isNull(nullif(rn % 5, 0),5)
from	cte

Be One with the Optimizer
TG

This is a better way to do it than what I posted. Thanks TG.

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-09 : 18:00:18

Thanks do both of you who helped me on this.

This works but I have one more question if I might. I am not familiar with the syntax
; with cte....

so I am having difficult wrapping this in an insert statement as I want to insert these results into a table:

Declare @Wt Table (
ID int not null default 0,
countnum int

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-09 : 18:41:23

Just put it in front of the SELECT as you would any insert:


;with cte (id, rn) as
(
select id, row_number() over (partition by id order by id)
from	@t
)
INSERT myTable (id, nbr)
select	id
	,isNull(nullif(rn % 5, 0),5)
from	cte

The ";" really belongs at the end of the preceding statement. It is not really part of the WITH structure.

Be One with the Optimizer
TG

jdaman
Constraint Violating Yak Guru

354 Posts

Posted - 2008-01-09 : 19:22:21

Or if you just want the logic in your select statement only:

select id, isnull(nullif(row_number() over (partition by id order by id) % 5, 0),5)
from YourTable

smh
Yak Posting Veteran

94 Posts

Posted - 2008-01-09 : 20:30:39

Thanks again. Works perfectly and I have learned how to use partition.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources