## help regarding selecting the maximum value

 Author Message Smash125 SSC Veteran Group: General Forum Members Points: 283 Visits: 1381 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 SSChampion Group: General Forum Members Points: 10152 Visits: 6378 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` Want an answer fast? Try hereHow to post data/code for the best help - Jeff ModenWhen a question, really isn't a question - Jeff SmithNeed a string splitter, try this - Jeff ModenHow to post performance problems - Gail ShawCrossTabs-Part1 & Part2 - Jeff ModenSQL Server Backup, Integrity Check, and Index and Statistics Maintenance - Ola HallengrenManaging Transaction Logs - Gail ShawTroubleshooting SQL Server: A Guide for the Accidental DBA - Jonathan Kehayias and Ted Krueger Smash125 SSC Veteran Group: General Forum Members Points: 283 Visits: 1381 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 SSChampion Group: General Forum Members Points: 10152 Visits: 6378