## help regarding selecting the maximum value

 Author Message Smash125 UDP Broadcaster Group: General Forum Members Points: 1479 Visits: 1392 Please find the test data as below.finding tough to come up with logic.Would appreciate greatly if some could helpcreate table #data(contactid int,totaldue money,rn int,maxrn int)select 1, 49846.693, 1, 1union allselect 1, 43214.9511, 2, 2union allselect 2, 43962.7901, 1, 1union allselect 2, 42123.1691, 2, 2union allselect 3, 89409.6319, 1, 1union allselect 3, 82078.0355, 2, 2union allselect 4, 27162.5876, 1, 1I want the o/p likecontactid,totaldue,rn,maxrn1,43214,2,22,42123.1691,2,23,82078.0355,2,24,27162.5876,1,1 anthony.green SSC Guru Group: General Forum Members Points: 63120 Visits: 8598 A good case for ROW_NUMBER()`drop table #datacreate table #data(contactid int,totaldue money,rn int,maxrn int)insert into #dataselect 1, 49846.693, 1, 1union allselect 1, 43214.9511, 2, 2union allselect 2, 43962.7901, 1, 1union allselect 2, 42123.1691, 2, 2union allselect 3, 89409.6319, 1, 1union allselect 3, 82078.0355, 2, 2union allselect 4, 27162.5876, 1, 1select * 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` How to post data/code for the best help - Jeff ModenNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger Smash125 UDP Broadcaster Group: General Forum Members Points: 1479 Visits: 1392 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 Group: General Forum Members Points: 63120 Visits: 8598 What do you want to make dynamic?But yes if you write dynamic SQL then you can make it dynamic. How to post data/code for the best help - Jeff ModenNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger