InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comWed, 22 May 2013 09:44:04 GMT20http://www.sqlservercentral.com/Forums/Topic1342746-392-1.aspxLooking at the execution plan, group by version looks simplier, 4 operators versus 6. The table scan and sort have the same cost associated in both.Thu, 09 Aug 2012 10:36:30 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1342746-392-1.aspxCurious, which is the more scalable solution?[code="sql"]DECLARE @persons TABLE(personid INT,version INT);INSERT @personsVALUES(1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(4,1),(4,2);SELECTpersonid,maxversion = MAX(version)FROM @personsGROUP BY personidORDER BY personid;WITH BaseData AS (SELECT personid, version, ROW_NUMBER() OVER (PARTITION BY personid ORDER BY version DESC) rnFROM @persons)SELECT personid, versionFROM BaseDatawhere rn = 1;[/code]I guess I'll have to test this at home tonight.Thu, 09 Aug 2012 09:58:49 GMTLynn Pettishttp://www.sqlservercentral.com/Forums/Topic1342746-392-1.aspx[quote][b]info 58414 (8/9/2012)[/b][hr]Hi,how can I get effectively max(version) to identify any PersonID with multiple VersionID?[/quote]That's what GROUP BY and MAX are for :)[code]DECLARE @persons TABLE(personid INT,version INT)INSERT @personsVALUES(1,1),(1,2),(1,3),(2,1),(2,2),(3,1),(4,1),(4,2)SELECTpersonid,maxversion = MAX(version)FROM @personsGROUP BY personidORDER BY personid[/code]Thu, 09 Aug 2012 09:16:50 GMTmtassinhttp://www.sqlservercentral.com/Forums/Topic1342746-392-1.aspxHi,how can I get effectively max(version) to identify any PersonID with multiple VersionID?[u]PersonID version[/u]1 11 21 32 12 23 14 14 2and so on ...Result1 32 23 14 2Thank youNicole:)Thu, 09 Aug 2012 09:10:16 GMTinfo 58414