CODEWITHSUNDEEP
sql-server
user8091579
user8091579

Reputation: 13

SQL update of grouped data using an aggregate

I'm trying to update a table by first grouping the data by column1, within each group of data I need to: sort the data by column2, populate Column4 with the top value from column3 (as sorted by column2) Columns1,3,4 are varchar and Column 3 is an int. I tried an update with a CTE and Top as:

Update a
Set a.col4 = c.Best
From Table1 a,
(Select Top (1) Col3 as Best, Col1, Col2
From Table1
Group By Col1
Order By Col2 DESC) c
where a.Col1 = c.Col1

but it seems to be selecting the top value from the table not from each group. Does anyone know what is missing here or an easier way of doing this?
Starting Data:
Col1 Col2 Col3 Col4
unit101 11 unit118 NULL
unit101 13 unit125 NULL
unit101 12 unit135 NULL
unit107 11 unit168 NULL
unit107 10 unit199 NULL

Required result:
Col1 Col2 Col3 Col4
unit101 11 unit118 unit125
unit101 13 unit125 unit125
unit101 12 unit135 unit125
unit107 11 unit168 unit168
unit107 10 unit199 unit168

Column 4 needs to have the value from col3 where col 2 is at maximum value of the rows in the data as grouped by col1.

Upvotes: 1

Views: 127

Answers (2)

hardkoded
hardkoded

Reputation: 21617

You can use CROSS APPLY

Update a
Set a.col4 = c.Best
From Table1 a
 CROSS APPLY (Select Top (1) Col3 as Best, Col1, Col2
              From Table1 t2
              where a.Col1 = t2.Col1
              Order By Col2 DESC) c

Upvotes: 0

user1529235
user1529235

Reputation:

Use 'rank' to determine the 'best' and add that to your inner join. Below is a working exampole and provides the results you asked for

Update a
Set a.col4 = c.Best
From Table1 a
inner join
(
Select 
    Col3 as Best,
    Col1,
    BestRank=RANK()over(partition by Col1 order by Col2 desc )
From 
    Table1 b
) c on a.Col1 = c.Col1 and c.BestRank=1

Upvotes: 1

Related Questions