Reputation: 236
This may be a stupid question and very easy but i am very distracted right now, and didn't find any solution.
I have a table like this:
ID | Value | Gr_Id | Gr_Value
------------------------------
a |0 |1 |Null
b |2 |2 |Null
c |4 |2 |Null
d |1 |3 |Null
e |3 |4 |Null
f |3 |4 |Null
g |2 |5 |Null
h |3 |5 |Null
Desired Output:
ID | Value | Gr_Id | Gr_Value
------------------------------
a |0 |1 |0
b |2 |2 |4
c |4 |2 |4
d |1 |3 |1
e |3 |4 |3
f |3 |4 |3
g |2 |5 |3
h |3 |5 |3
So i want to update the group value and set the maximum value of the group_id. Thank you.
Upvotes: 3
Views: 65
Reputation: 3752
create table Gtable(ID varchar, Value int , Gr_Id int, Gr_Value int)
Insert into Gtable Values('a', 0, 1, null)
Insert into Gtable Values('b', 2, 2, null)
Insert into Gtable Values('c', 4, 2, null)
Insert into Gtable Values('d', 1, 3, null)
Insert into Gtable Values('e', 3, 4, null)
Insert into Gtable Values('f', 3, 4, null)
Insert into Gtable Values('g', 2, 5, null)
Insert into Gtable Values('h', 3, 5, null)
select A.Id, A.Value, A.Gr_Id, C.maxV Gr_Value from Gtable A
JOIN
( select A.Gr_Id, max(B.Value) maxV from
( select Distinct Gr_Id from Gtable ) A
JOIN Gtable B On A.Gr_Id=B.Gr_Id
Group by A.Gr_Id
) C
On A.Gr_Id=C.Gr_Id
Id Value Gr_Id Gr_Value
a 0 1 0
b 2 2 4
c 4 2 4
d 1 3 1
e 3 4 3
f 3 4 3
g 2 5 3
h 3 5 3
Upvotes: 0
Reputation: 11566
Query
UPDATE t1
SET t1.Gr_Value = t2.val
FROM tblGroup t1
JOIN (SELECT Gr_Id, MAX(Value) AS val FROM tblGroup GROUP By Gr_Id) t2
ON t1.Gr_Id = t2.Gr_Id;
Upvotes: 0
Reputation: 44356
Try this using common table expression:
CREATE TABLE #t
(ID char,Value int, Gr_Id int, gr_value int)
INSERT #t(id, value, gr_id)
values
('a',0,1),('b',2,2),('c',4,2),('d',1,3),
('e',3,4),('f',3,4),('g',2,5),('h',3,5)
;WITH CTE as
(
SELECT
gr_value,
max(value) over(partition by gr_id) max_gr_value
FROM #t
)
UPDATE CTE SET gr_value = max_gr_value
SELECT * FROM #t
Result:
ID Value Gr_Id Gr_value
a 0 1 0
b 2 2 4
c 4 2 4
d 1 3 1
e 3 4 3
f 3 4 3
g 2 5 3
h 3 5 3
Upvotes: 1
Reputation: 11
This should do it:
UPDATE MyTable --whatever your table is called
SET Gr_Value = MaxValues.Value
FROM MyTable
INNER JOIN (
SELECT Gr_Id, MAX(Value) AS Value
FROM MyTable
GROUP BY Gr_Id) AS MaxValues ON MaxValues.Gr_Id = MyTable.Gr_Id
Upvotes: 0
Reputation: 12309
Using OUTER APPLY
you can do this
SELECT ID,Value,Gr_Id,M.Gr_Value
FROM URTable
OUTER APPLY
(
SELECT MAX (Value) as Gr_Value
FROM URTable tmp
WHERE tmp.Gr_Id=URTable.Gr_Id
)M
Upvotes: 1
Reputation: 9869
I think this will solve your problem:
SELECT ID, Value, Gr_Id, (
SELECT MAX(Value)
FROM tableName t2
WHERE t1.Gr_Id = t2.Gr_Id
) as Gr_Value
FROM tableName t1
Try it; hope it helps
Upvotes: 1