Group By and update each group rows

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)

Group By and update each group rows

Author

Topic

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 04:35:32

Table having three columns -
Counter UserId ManagerId
0 58DC2B19 1F320772
0 66B7C6F8 1F320772
0 D0D2F548 1F320772
0 F76C81E2 1F320772
0 58DC2B19 9799FFD8
0 1F320772 58DC2B19
1 55A77406 1F320772
1 66B7C6F8 55A77406
1 D0D2F548 66B7C6F8
1 F76C81E2 D0D2F548
1 58DC2B19 F76C81E2
0 9799FFD8 58DC2B19
1 1F320772 9799FFD8
0 1F320772 F76C81E2
0 1F320772 D0D2F548
0 1F320772 66B7C6F8
0 9799FFD8 F76C81E2
0 9799FFD8 D0D2F548
0 9799FFD8 66B7C6F8

From above result , i have same IDs in both UserId column and ManagerId column, i would like to group the results in such a way that all rows are grouped by UserId and ManagerId together

Example –

counter UserId ManagerId
0 58DC2B19 1F320772
0 58DC2B19 9799FFD8
0 1F320772 58DC2B19
1 58DC2B19 F76C81E2
0 9799FFD8 58DC2B19
0 66B7C6F8 1F320772
1 66B7C6F8 55A77406
1 D0D2F548 66B7C6F8
0 1F320772 66B7C6F8
................................
So on for other Ids...

Once i group rows, for each group rows, i have to get max counter value in a group and update other rows in same group with a counter value greater than the max counter value of same group.Same thing applies for other groups.

Example –

counter UserId ManagerId
2 58DC2B19 1F320772
3 58DC2B19 9799FFD8
4 1F320772 58DC2B19
1 58DC2B19 F76C81E2
5 9799FFD8 58DC2B19
2 66B7C6F8 1F320772
1 66B7C6F8 55A77406
3 D0D2F548 66B7C6F8
4 1F320772 66B7C6F8
..... So on for other Ids.

Can some one help in query please.

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 05:40:01

Why is F76C81E2,55A77406 not appearing as userid in the resultset you posted?


counter UserId ManagerId
0 58DC2B19 1F320772
0 58DC2B19 9799FFD8
0 1F320772 58DC2B19
1 58DC2B19 F76C81E2
0 9799FFD8 58DC2B19
0 66B7C6F8 1F320772
1 66B7C6F8 55A77406
1 D0D2F548 66B7C6F8
0 1F320772 66B7C6F8

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 06:05:05

quote:
Originally posted by Sachin.Nand

Why is F76C81E2,55A77406 not appearing as userid in the resultset you posted?

counter UserId ManagerId
0 58DC2B19 1F320772
0 58DC2B19 9799FFD8
0 1F320772 58DC2B19
1 58DC2B19 F76C81E2
0 9799FFD8 58DC2B19
0 66B7C6F8 1F320772
1 66B7C6F8 55A77406
1 D0D2F548 66B7C6F8
0 1F320772 66B7C6F8

PBUH

You talking about this rows ?
0 F76C81E2 1F320772
1 F76C81E2 D0D2F548
These rows will not come with respective to '58DC2B19' because
but ''58DC2B19' doesn't appear neither as UserId or ManagerId.
It might list with respective to other ID.

Same reason for '55A77406 '

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 06:22:19

But why w.r.t '58DC2B19'?

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 06:30:35

quote:
Originally posted by Sachin.Nand

But why w.r.t '58DC2B19'?

PBUH

Sorry, please ignore ManagerId column itself.
Now we have only counter and UserId column in table.
Now i need group by UserId and in each group rows i need to update counter value.

Counter UserId
0 58DC2B19
0 66B7C6F8
0 D0D2F548
0 F76C81E2
0 58DC2B19
0 1F320772
1 55A77406
1 66B7C6F8
1 D0D2F548
1 F76C81E2
1 58DC2B19
0 9799FFD8
1 1F320772
0 1F320772
0 1F320772
0 1F320772
0 9799FFD8
0 9799FFD8
0 9799FFD8

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 06:46:47

Ok last one more question.
What should be the o/p & will the counter be always 0 & 1 initially?

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 07:14:24

quote:
Originally posted by Sachin.Nand

Ok last one more question.
What should be the o/p & will the counter be always 0 & 1 initially?

PBUH

I have query which get me below result .i.e order the result by UserId.
Select Counter, UserId from table order by UserId, Counter
Counter UserId
0 58DC2B19
0 58DC2B19
1 58DC2B19
0 66B7C6F8
1 66B7C6F8
0 D0D2F548
1 D0D2F548
0 F76C81E2
1 F76C81E2
1 55A77406
1 1F320772
0 1F320772
0 1F320772
0 1F320772
0 1F320772
0 9799FFD8
0 9799FFD8
0 9799FFD8
0 9799FFD8

So now for each rows grouped/ordered by userId, i have to get max counter value in a group and update other rows in same group
with a counter value greater than the max counter value of same group
Counter UserId
3 58DC2B19
2 58DC2B19
1 58DC2B19
2 66B7C6F8
1 66B7C6F8
2 D0D2F548
1 D0D2F548
2 F76C81E2
1 F76C81E2
3 55A77406
4 1F320772
0 1F320772
1 1F320772
2 1F320772
3 1F320772
4 9799FFD8
5 9799FFD8
6 9799FFD8
7 9799FFD8

Counter can be any number initially.

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 07:18:17

Maybe this


declare @tbl as table(Counter int, UserId varchar(30))
insert into @tbl
select 0, '58DC2B19' union all  
select 0, '66B7C6F8' union all 
select 0, 'D0D2F548' union all 
select 0, 'F76C81E2' union all 
select 0, '58DC2B19' union all 
select 0, '1F320772' union all 
select 1, '55A77406' union all 
select 1, '66B7C6F8' union all 
select 1, 'D0D2F548' union all 
select 1, 'F76C81E2' union all 
select 1, '58DC2B19' union all 
select 0, '9799FFD8' union all 
select 3, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '9799FFD8' union all 
select 0, '9799FFD8' union all 
select 0, '9799FFD8'

select * from @tbl order by UserId,Counter

select userid,Counter OldCounter,cnt+ROW_NUMBER()over(partition by userid,cnt order by userid)-1 Newcounter  from @tbl t
cross apply
(
   select top 1(Counter)cnt from @tbl t1 where t.UserId=t1.UserId order by counter desc,userid

)T1

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 07:30:43

quote:

Originally posted by Sachin.Nand

Maybe this


declare @tbl as table(Counter int, UserId varchar(30))
insert into @tbl
select 0, '58DC2B19' union all  
select 0, '66B7C6F8' union all 
select 0, 'D0D2F548' union all 
select 0, 'F76C81E2' union all 
select 0, '58DC2B19' union all 
select 0, '1F320772' union all 
select 1, '55A77406' union all 
select 1, '66B7C6F8' union all 
select 1, 'D0D2F548' union all 
select 1, 'F76C81E2' union all 
select 1, '58DC2B19' union all 
select 0, '9799FFD8' union all 
select 3, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '1F320772' union all 
select 0, '9799FFD8' union all 
select 0, '9799FFD8' union all 
select 0, '9799FFD8'

select * from @tbl order by UserId,Counter

select userid,Counter OldCounter,cnt+ROW_NUMBER()over(partition by userid,cnt order by userid)-1 Newcounter  from @tbl t
cross apply
(
   select top 1(Counter)cnt from @tbl t1 where t.UserId=t1.UserId order by counter desc,userid

)T1

PBUH

You are incrementing/updating the row from which we get max counter value. That's shouldb't happen. We should update only other rows of same group.

I need to update this in table. Here we only selecting and displaying it.

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 07:42:33

This is really a ridiculous requirement for me.Look at this part of your data o/p.

Why would 9799FFD8 start with 4 when the max counter for 9799FFD8 is 0?
By the way what is your definition of group?
In SQL terms shouldn't it be a "group of users"?

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 07:47:18

quote:
Originally posted by Sachin.Nand

This is really a ridiculous requirement for me.Look at this part of your data o/p.

0 1F320772
1 1F320772
2 1F320772
3 1F320772
4 9799FFD8
5 9799FFD8
6 9799FFD8
7 9799FFD8

Why would 9799FFD8 start with 4 when the max counter for 9799FFD8 is 0?
By the way what is your definition of group?
In SQL terms shouldn't it be a "group of users"?

PBUH

No sorry it was mistake in o/p for that particular Id.
I confused you by using 'Group' term here.
"group of users" - I meant group of rows of same UserId.

Updated o/p -
3 58DC2B19
2 58DC2B19
1 58DC2B19
2 66B7C6F8
1 66B7C6F8
2 D0D2F548
1 D0D2F548
2 F76C81E2
1 F76C81E2
3 55A77406
4 1F320772
0 1F320772
1 1F320772
2 1F320772
3 1F320772
0 9799FFD8
1 9799FFD8
2 9799FFD8
3 9799FFD8

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 08:04:04

Again ur o\p data is wrong.Look at this part.

Now for 1F320772 max counter starts with 1 then why should it contain 0 in the above o/p?

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 08:10:39

quote:
Originally posted by Sachin.Nand

Again ur o\p data is wrong.Look at this part.

4 1F320772
0 1F320772
1 1F320772
2 1F320772
3 1F320772

Now for 1F320772 max counter starts with 1 then why should it contain 0 in the above o/p?

PBUH

yes ur right, i hope you understand my question.
Sorry about that..

Before update -
1 1F320772
0 1F320772
0 1F320772
0 1F320772
0 1F320772

After update -
1 1F320772
2 1F320772
3 1F320772
4 1F320772
5 1F320772

Notice first row wasn't updated.
I need update query.

Sachin.Nand

2937 Posts

Posted - 2010-09-28 : 08:46:31

Gee you really put my brains out on this one


declare @tbl as table(id int identity,Counter int, UserId varchar(30),newcounter int)
insert into @tbl
select 0, '58DC2B19',null union all
select 0, '58DC2B19',null union all 
select 1, '58DC2B19',null union all
select 0, '66B7C6F8',null union all
select 1, '66B7C6F8',null union all 
select 0, 'D0D2F548',null union all 
select 1, 'D0D2F548',null union all 
select 0, 'F76C81E2',null union all 
select 1, 'F76C81E2',null union all 
select 1, '55A77406',null union all 
select 1, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null 
update t2 set t2.newcounter=t.newcounter from @tbl t2
inner join
(
select id,userid,Counter OldCounter,cnt+ROW_NUMBER()over(partition by userid,cnt order by userid)-1 Newcounter  from @tbl t
cross apply
(
   select top 1(Counter)cnt from @tbl t1 where t.UserId=t1.UserId order by counter desc,userid

)T1
)T on T.UserId=t.UserId and t.id=t2.id

select userid,Counter,newcounter from @tbl order by userid,newCounter

PBUH

deepak_Dotnet
Starting Member

32 Posts

Posted - 2010-09-28 : 09:01:21

quote:

Originally posted by Sachin.Nand

Gee you really put my brains out on this one


declare @tbl as table(id int identity,Counter int, UserId varchar(30),newcounter int)
insert into @tbl
select 0, '58DC2B19',null union all
select 0, '58DC2B19',null union all 
select 1, '58DC2B19',null union all
select 0, '66B7C6F8',null union all
select 1, '66B7C6F8',null union all 
select 0, 'D0D2F548',null union all 
select 1, 'D0D2F548',null union all 
select 0, 'F76C81E2',null union all 
select 1, 'F76C81E2',null union all 
select 1, '55A77406',null union all 
select 1, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '1F320772',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null union all 
select 0, '9799FFD8',null 
update t2 set t2.newcounter=t.newcounter from @tbl t2
inner join
(
select id,userid,Counter OldCounter,cnt+ROW_NUMBER()over(partition by userid,cnt order by userid)-1 Newcounter  from @tbl t
cross apply
(
   select top 1(Counter)cnt from @tbl t1 where t.UserId=t1.UserId order by counter desc,userid

)T1
)T on T.UserId=t.UserId and t.id=t2.id

select userid,Counter,newcounter from @tbl order by userid,newCounter

PBUH

Thanks a lot , i am checking with huge data.
I will reply u soon.

Thanks again, it is working

Sachin.Nand

2937 Posts

Posted - 2010-09-29 : 05:02:07

Welcome.

But did it take a day for the query to run?

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources