Select the most recent entry in the child.

Question

I have the following tables / columns:

Parent: ParentID Child: ChildID ParentID SubChild: SubChildID ChildID Date 

Parent has a relationship of 1 to many with Child

Child has 1 to many SubChild with SubChild

For each Parent I need to get SubChild with the most recent Date value. How can I do this using SQL. I tried using MAX(Date) , but I cannot figure out how to successfully join Parent and Child .

An ideal result set would contain all the Parent combined with all the SubChild columns of the last record.

Note: using MS SQL 2005 +

Answer 1

Look at using ROW_NUMBER

Sort of

 ;WITH Vals AS ( SELECT p.ParentID, sc.SubChildID, ROW_NUMBER() OVER (PARTITION BY p.ParentID ORDER BY sc.[Date] DESC) RowID FROM Parent p INNER JOIN Child c ON p.ParentID = c.ParentID INNER JOIN SubChild sc ON c.ChildID = sc.ChildID ) SELECT ParentID, SubChildID FROM Vals WHERE RowID = 1 

Answer 2

For some data distributions, this approach may be faster.

 SELECT p.ParentID, sc.SubChildID, sc.Date FROM Parent p CROSS APPLY (SELECT TOP(1) s.SubChildID, s.Date FROM SubChild s JOIN Child c ON c.ChildID = s.ChildID WHERE c.ParentID=p.ParentID ORDER BY s.Date DESC) sc 

Answer 3

You can do this using a correlated subquery, but use SQL Server 2005 ranking functions faster. As long as you know what you are doing, that is.

Answer 4

To keep things simple, you can make a sub-selection. In my testing, this has the same performance as the cross-apply approach:

 select firstname, lastname, (Select top 1 Display from _notes where _notes.ParentId = c.Id order by createdon desc) as MostRecentNote from _contacts c 

For 47,000 records, this subsampling approach takes about 4 seconds. To do this quickly, I added a sorted index on _Notes, which included ParentId, CreatedOn (Sorted descending), and the Display column was turned on). This reduces the query to less than 1 second by 47 thousand records.