asked on

Populate fields in group based on first record in group

Hello,

I have a table, with records sorted by ID field:

ID TeamID TeamRecord
1 111 1
2 0 0
3 0 0
4 555 1
5 0 0
6 0 0
7 333 1
8 0 0

I need an update query, which will make this table to look like:

ID TeamID TeamRecord
1 111 1
2 111 0
3 111 0
4 555 1
5 555 0
6 555 0
7 333 1
8 333 0

Please help..
Thank you!

HainKurt

try this

update mytable a
set teamid = (select top 1 b.teamid from myTable b where b.id<a.id and b.teamid<>0 order by b.id desc)
where teamid=0

Open in new window

ASKER CERTIFIED SOLUTION

HainKurt

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Pavlo

ASKER

cool, thank you!

Sharath S

Another approach.

create table test(ID int, TeamID int, TeamRecord int)
insert into test values 
(1,  111,          1),
(2,  0  ,           0),
(3,  0  ,            0),
(4,  555,          1),
(5,  0  ,            0),
(6,  0  ,            0),
(7,  333,          1),
(8,  0  ,            0)

;with cte1 as (select *,case when TeamID <> 0 then ID end Alt_ID from test),
      cte2 as (select *,max(Alt_ID) over (order by ID rows unbounded preceding) as grp from cte1),
      cte3 as (select *,max(TeamID) over (partition by grp) TeamID2 from cte2)
update cte3 set TeamID = TeamID2

select * from test
/*
  	ID	TeamID	TeamRecord
1	1	111	1
2	2	111	0
3	3	111	0
4	4	555	1
5	5	555	0
6	6	555	0
7	7	333	1
8	8	333	0
*/

Open in new window