Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login
Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
Join Tek-Tips
*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

LINK TO THIS FORUM!

Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

Tek-Tips LinkedIn® Group

Partner With Us!

"Best Of Breed" Forums Add Stickiness To Your Site
Partner Button
(Download This Button Today!)

Feedback

"...If there has ever been a justification needed for access to the net during working hours, just referring to this site should suffice. Fantastic!..."
More...

Geography

Where in the world do Tek-Tips members come from?
Click Here To Find Out!

Tek-Tips Shirts!

Get Tek-Tips Swag
Get your
Tek-Tips Forums
SWAG here!
Home > Forums > Programmers > DBMS Packages > Microsoft SQL Server: Programming Forum

Update maximum value of each groupHelpful Member! 

thread183-1687769
Read More Threads Like This One
bmacbmac (IS/IT--Management)
9 Jul 12 20:06
Hello. I have the following table/Data:

CODE

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...

CODE

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

Thanks!
Helpful Member!  gmmastros (Programmer)
9 Jul 12 20:32
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:

CODE

;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:

CODE

;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 (Programmer)
10 Jul 12 21:00
You don't need to join back to CTE, you can UPDATE cte directly, e.g.

CODE

UPDATE MaxCounter SET NewILandId = ILandId WHERE RowID = 1

PluralSight Learning Library

Read More Threads Like This One

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Back To Forum

Back To Microsoft SQL Server: Programming

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close