CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Stad
Stad

Reputation: 236

Set the maximum value of the group to each group id

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

Answers (6)

Anil
Anil

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

Ullas
Ullas

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

t-clausen.dk
t-clausen.dk

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

Ben
Ben

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

Jaydip Jadhav
Jaydip Jadhav

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

mortb
mortb

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

Related Questions