Post reply

Return Max value for each Group set

  • 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,

  • May 10, 2011 at 7:48 am

    #1322307

    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

  • May 10, 2011 at 8:32 am

    #1322350

    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

  • May 10, 2011 at 8:46 am

    #1322359

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

  • May 10, 2011 at 8:48 am

    #1322364

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

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

    Follow me on Twitter: http://www.twitter.com/way0utwest
    Forum Etiquette: How to post data/code on a forum to get the best help
    My Blog: www.voiceofthedba.com

  • May 10, 2011 at 8:51 am

    #1322365

    Great - thanks for the link Steve

  • May 10, 2011 at 9:16 am

    #1322394

    Excellent responses guys. Thanks

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

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