Update maximum value of each group 1

[bmacbmac]

Hello. I have the following table/Data:

drop table #temp
create table #temp (counter int, irecordid int, ilandid int, Info varchar(100), newilandid int)
insert into #temp values (163309, 111111, 518725, '&_&_ASHFORD', NULL)
insert into #temp values (1160900, 111111, 4493505, '&_&_ASHFORD', NULL)
insert into #temp values (1160901, 111111, 511234, '&_&_ASHFORD', NULL)
insert into #temp values (1951985, 111111, 6443293, '&_&_ASHFORD', NULL)
insert into #temp values (2081903, 111111, 5541133, '&_&_ASHFORD', NULL)
insert into #temp values (1981123, 222222, 9987642, '1_2_SMITH', NULL)
insert into #temp values (1995001, 222222, 1132223, '1_2_SMITH', NULL)
insert into #temp values (2011987, 222222, 11344, '1_2_SMITH', NULL)
insert into #temp values (3123766, 222222, 5587, '1_2_SMITH', NULL)

I would like to update the newilandid field. I would like to set the newilandid field = the ilandid of the MAX counter of each group based on irecordid, info


so hoping to end up with this:

counter, irecordid, ilandid, info, newilandid
163309,111111,518725,&_&_ASHFORD,5541133
1160900,111111,4493505,&_&_ASHFORD,5541133
1160901,111111,511234,&_&_ASHFORD,5541133
1951985,111111,6443293,&_&_ASHFORD,5541133
2081903,111111,5541133,&_&_ASHFORD,5541133

1981123,222222,9987642,1_2_SMITH,5587
1995001,222222,1132223,1_2_SMITH,5587
2011987,222222,11344,1_2_SMITH,5587
3123766,222222,5587,1_2_SMITH,5587

I have been trying some inner join stuff, but can't quite come up with it...

select * from #temp a
inner join #temp b
on a.info = b.info
and a.irecordid = b.irecordid
and a.counter = b.counter

Thanks!

 

[gmmastros]

You should think of this query in parts. For the first part, you should construct a query that returns the iRecordId and the iLandId for the Max(Counter), like this:

;With MaxCounter As
(
  Select  iRecordId,
          iLandId,
          Row_Number() Over (Partition By iRecordId Order By Counter DESC) As RowID
  From    #Temp
)
Select *
From   MaxCounter
Where  RowId = 1

Once you have this, you simply join back to your original table based on the iRecordId to do the update, like this:

;With MaxCounter As
(
  Select  iRecordId,
          iLandId,
          Row_Number() Over (Partition By iRecordId Order By Counter DESC) As RowID
  From    #Temp
)
Update  #Temp
Set     #Temp.NewILandId = MaxCounter.iLandId
From    #Temp
        Inner Join MaxCounter
          On #Temp.iRecordId = MaxCounter.iRecordId
          And MaxCounter.RowId = 1

There are (of course) many different ways this could be written. This is just one example.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

You don't need to join back to CTE, you can UPDATE cte directly, e.g.

UPDATE MaxCounter SET NewILandId = ILandId WHERE RowID = 1

PluralSight Learning Library