InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 12:53:21 GMT20http://www.sqlservercentral.com/Forums/Topic1405382-392-1.aspxWhat do you want to make dynamic?But yes if you write dynamic SQL then you can make it dynamic.Thu, 10 Jan 2013 06:48:00 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1405382-392-1.aspxPerfect !!! is it possible to come up with some dynamic query here we are selecting the rows based on the row number(rn)Thu, 10 Jan 2013 06:45:46 GMTSmash125http://www.sqlservercentral.com/Forums/Topic1405382-392-1.aspxA good case for ROW_NUMBER()[code="sql"]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[/code]Thu, 10 Jan 2013 06:36:36 GMTanthony.greenhttp://www.sqlservercentral.com/Forums/Topic1405382-392-1.aspxPlease 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,1Thu, 10 Jan 2013 06:29:53 GMTSmash125