InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comMon, 20 May 2013 00:47:18 GMT20http://www.sqlservercentral.com/Forums/Topic1381154-391-1.aspxYou could read about windowing function and learn how ti use them.Your example can be solved like this:[code="sql"]SELECT ss.*FROM( Select s.PropertyStatusDate ,s.comments ,s.rmVvlPropertyStatus ,s.rvlPropertyStatus.rvlpropertyid ,s.rvlPropertyStatusID ,rnum = ROW_NUMBER() OVER(PARTITION BY s.rvlPropertyID ORDER BY s.PropertyStatusDate DESC) From dbo.rvlPropertyStatus s) ssWHERE ss.rnum = 1[/code]Please use aliases.Kind regards,VedranMon, 05 Nov 2012 17:32:51 GMTVedran Kesegichttp://www.sqlservercentral.com/Forums/Topic1381154-391-1.aspxOk, I have a solution for my first question. I am sure there must be a better way to do this, but this is what I've got so far:[code="sql"]Exec dbo.upObjectQuickDrop @ObjectName = 'TempDataHolder' ,@ObjectType = 'Table' ,@Schema = 'TempDB';GoWith CTE (NestRowNumber ,rvlPropertyID)As (Select Row_Number() Over(Order By rvlPropertyID) ,rvlPropertyID From dbo.rvlPropertyStatus)Select Max(NestRowNumber) As RowNumber ,rvlPropertyIDInto #TempDataHolderFrom CTEGroup By rvlPropertyIDOrder By rvlPropertyID;GoExec dbo.upObjectQuickDrop @ObjectName = 'TempResultsSet' ,@ObjectType = 'Table' ,@Schema = 'TempDB';GoSelect (Row_Number() Over(Order By rvlPropertyID)) As RowNumber ,PropertyStatusDate ,comments ,rmVvlPropertyStatus ,rvlPropertyStatus.rvlpropertyid ,rvlPropertyStatusIDInto #TempResultsSetFrom dbo.rvlPropertyStatusSelect TRS.RowNumber ,PropertyStatusDate ,comments ,rmVvlPropertyStatus ,TRS.rvlPropertyID ,rvlPropertyStatusIDFrom #TempResultsSet TRS Inner Join #TempDataHolder TDH On TRS.rvlPropertyID = TDH.rvlPropertyID And TRS.RowNumber = TDH.RowNumber[/code]Any thoughts? Any ideas where I may be overlooking a potential logic bomb?Mon, 05 Nov 2012 09:47:15 GMTjarid.lawsonhttp://www.sqlservercentral.com/Forums/Topic1381154-391-1.aspxI am asking the question as I begin the research on how to do this because I am not sure if I will figure this out on my own. I have not used the Row_Number() function that often, and most of those times it was after an old mentor of mine had written it for me.Here's what I am trying to achieve. I am re-working a nested derived table:[code="sql"]Select PropertyStatusDate ,comments ,rmVvlPropertyStatus ,rvlPropertyStatus.rvlpropertyid ,rvlPropertyStatusIDFrom dbo.rvlPropertyStatus Inner Join (Select Max(PropertyStatusDate) As maxDate ,rvlPropertyID From dbo.rvlPropertyStatus Group By rvlPropertyID) As maxStatus On rvlPropertyStatus.PropertyStatusDate = maxStatus.maxDate And rvlPropertyStatus.rvlPropertyID = maxStatus.rvlPropertyID[/code]In my system the joined derived table return 17,635 records, but the entire join returns 17,637 records. I believe this is being caused by 2 instances where the same property ID has two instances of the same date.My thought is to use Row_Number() to accomplish this. I believe I know how to do this, but that is still in the conceptual phase.My question is in two parts:1. - The obvious question of how. I will be working on that as soon as I post this, but I don't have an exceeding level of confidence in my skills with this function yet.2. - Is there a better means of achieving my goal? In other words what are the places where this is going to turn around and bite me that I haven't thought of yet?Mon, 05 Nov 2012 09:22:12 GMTjarid.lawson