Return Max value for each Group set

Question

Return Max value for each Group set

DerbyNeal

Hall of Fame

Points: 3854
More actions
May 10, 2011 at 7:29 am

#239336

Hi,
I have a column (MaxOrderRank) in a temporary table (@Results) which I need to update using values in other columns within the same table.
There is another field which contains a ranking order. I need to populate ‘MaxOrderRank’ with the maximum ‘IndivOrderRank’ value for each Rep, ProductGroup and CustAcc group.
Example:
Before update
Rep, ProductGroup, CustAcc, OrderNo , IndivOrderRank, MaxOrderRank
PeterB, Oil, 04455, 34A, 1, 0
PeterB, Oil, 04455, 55A, 2, 0
PeterB, Oil, 04455, 82A, 3, 0
RogerP, Pro, 03477, 23A, 1, 0
RogerP, Pro, 03477, 26A, 2, 0
PeterB, Pro, 03792, 14A, 1, 0
PeterB, Pro, 04325, 35A, 1, 0
PeterB, Pro, 04325, 42A, 2, 0
After update
Rep, ProductGroup, CustAcc, OrderNo , IndivOrderRank, MaxOrderRank
PeterB, Oil, 04455, 34A, 1, 3
PeterB, Oil, 04455, 55A, 2, 3
PeterB, Oil, 04455, 82A, 3, 3
RogerP, Pro, 03477, 23A, 1, 2
RogerP, Pro, 03477, 26A, 2, 2
PeterB, Pro, 03792, 14A, 1, 1
PeterB, Pro, 04325, 35A, 1, 2
PeterB, Pro, 04325, 42A, 2, 2
Any ideas please?
Thanks in advance,

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

dave.repton SSCrazy Points: 2468 More actions · Answer 1

Not sure if this is the most efficient method, but the below should work - would help to see the rest of the query as it may be easier to populate in a previous operation...

Would also appreciate anyone elses comments on how efficient this is ?:-D

declare @results as

table (Rep varchar(10), ProductGroup varchar(10), CustAcc varchar(10), OrderNo varchar(10), IndivOrderRank int, MaxOrderRank int)

insert @results

select 'PeterB' Rep, 'Oil' ProductGroup, '04455' CustAcc, '34A' OrderNo, 1 IndivOrderRank, 0 MaxOrderRank

union all

select 'PeterB', 'Oil', '04455', '55A', 2, 0

union all

select 'PeterB', 'Oil', '04455', '82A', 3, 0

union all

select 'RogerP', 'Pro', '03477', '23A', 1, 0

union all

select 'RogerP', 'Pro', '03477', '26A', 2, 0

union all

select 'PeterB', 'Pro', '03792', '14A', 1, 0

union all

select 'PeterB', 'Pro', '03477', '35A', 1, 0

union all

select 'PeterB', 'Pro', '03477', '42A', 2, 0

update @Results

set MaxOrderRank = MxO

from @Results a

inner join

(

select Rep, ProductGroup, CustAcc, max(indivOrderrank) MxO

from @results

group by Rep, ProductGroup, CustAcc

) b

on a.productgroup = b.productgroup

and a.custacc = b.custacc

and a.rep = b.rep

select * from @results

Mark Cowne One Orange Chip Points: 26954 More actions · Answer 2

Also this

WITH CTE AS (

SELECT *,

MAX(IndivOrderRank) OVER(PARTITION BY Rep, ProductGroup, CustAcc) AS mx

FROM @results)

UPDATE CTE

SET MaxOrderRank=mx;

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537

dave.repton SSCrazy Points: 2468 More actions · Answer 3

Nice - the client stats for the two appear to be the same - any idea if with greater volumes yours would perform better ?

Steve Jones - SSC Editor SSC Guru Points: 736445 More actions · Answer 4

You might read this as well: http://www.sqlservercentral.com/articles/T-SQL/71571/

Talks about solving your issue and how the query above works.

dave.repton SSCrazy Points: 2468 More actions · Answer 5

dave.repton

SSCrazy

Points: 2468

May 10, 2011 at 8:51 am

#1322365

Great - thanks for the link Steve

DerbyNeal Hall of Fame Points: 3854 More actions · Answer 6

DerbyNeal

Hall of Fame

Points: 3854

May 10, 2011 at 9:16 am

#1322394

Excellent responses guys. Thanks