[RESOLVED] sql server aggregate query

[MMock]

So if this is my table being built:

GroupID	Status5RowId	CreateDateStatus5	Status6RowId	CreateDateStatus6
43        95             NULL                        NULL                       NULL
57        675            NULL                        NULL                       NULL

I next want to update EnrollStatus6RowId with 676, because 676 is my max GroupStatusId of my 6 records. But I get an error that I can't do a MAX in a SET (An aggregate may not appear in the set list of an UPDATE statement):

	
update @FinalStatus_5and6_RowID	
set Status6RowId = MAX(groupstatusid)
		from @FinalStatus_5and6_RowID A 
		inner join GroupStatus B
		on A.GroupID = B.GroupID
		and B.StatusID = 6