help regarding selecting the maximum value

  • Smash125

    Hall of Fame

    Points: 3527

    Please find the test data as below.finding tough to come up with logic.

    Would appreciate greatly if some could help

    create table #data

    (

    contactid int,

    totaldue money,

    rn int,

    maxrn int

    )

    select 1, 49846.693, 1, 1

    union all

    select 1, 43214.9511, 2, 2

    union all

    select 2, 43962.7901, 1, 1

    union all

    select 2, 42123.1691, 2, 2

    union all

    select 3, 89409.6319, 1, 1

    union all

    select 3, 82078.0355, 2, 2

    union all

    select 4, 27162.5876, 1, 1

    I want the o/p like

    contactid,totaldue,rn,maxrn

    1,43214,2,2

    2,42123.1691,2,2

    3,82078.0355,2,2

    4,27162.5876,1,1

  • anthony.green

    SSC Guru

    Points: 112515

    A good case for ROW_NUMBER()

    drop table #data

    create table #data

    (

    contactid int,

    totaldue money,

    rn int,

    maxrn int

    )

    insert into #data

    select 1, 49846.693, 1, 1

    union all

    select 1, 43214.9511, 2, 2

    union all

    select 2, 43962.7901, 1, 1

    union all

    select 2, 42123.1691, 2, 2

    union all

    select 3, 89409.6319, 1, 1

    union all

    select 3, 82078.0355, 2, 2

    union all

    select 4, 27162.5876, 1, 1

    select * from #data

    ;with cte as

    (

    select

    ROW_NUMBER() OVER(PARTITION BY ContactID ORDER BY RN DESC) AS RowNum,

    *

    FROM

    #data

    )

    select * from cte where rownum = 1

  • Smash125

    Hall of Fame

    Points: 3527

    Perfect !!! is it possible to come up with some dynamic query here we are selecting the rows based on the row number(rn)

  • anthony.green

    SSC Guru

    Points: 112515

    What do you want to make dynamic?

    But yes if you write dynamic SQL then you can make it dynamic.

Viewing 4 posts - 1 through 4 (of 4 total)

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