# 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)