Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 help regarding selecting the maximum value Rate Topic Display Mode Topic Options
Author
 Message
 Posted Thursday, January 10, 2013 6:29 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 29, 2016 10:35 AM Points: 215, Visits: 1,376
 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
Post #1405382
 Posted Thursday, January 10, 2013 6:36 AM
 SSCertifiable Group: General Forum Members Last Login: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
 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
Post #1405389
 Posted Thursday, January 10, 2013 6:45 AM
 SSC Veteran Group: General Forum Members Last Login: Thursday, September 29, 2016 10:35 AM Points: 215, Visits: 1,376
 Perfect !!! is it possible to come up with some dynamic query here we are selecting the rows based on the row number(rn)
Post #1405398
 Posted Thursday, January 10, 2013 6:48 AM
 SSCertifiable Group: General Forum Members Last Login: Thursday, September 1, 2016 2:56 AM Points: 5,969, Visits: 6,067
Post #1405400

 Permissions