CODEWITHSUNDEEP
sqlsql-serverjoin
Omar
Omar

Reputation: 40202

Select most recent record in sub child

I have the following tables/columns:

Parent:
  ParentID

Child:
  ChildID
  ParentID

SubChild:
  SubChildID
  ChildID
  Date

Parent has 1 to Many relationship with Child

Child has 1 to Many relationship with SubChild

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

The ideal result set would contain all the Parents joined with all the SubChild columns of the latest record.

Note: using MS SQL 2005+

Upvotes: 4

Views: 2607

Answers (4)

ProVega
ProVega

Reputation: 5914

To keep things simple, you can do a sub-select. 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 47k records, this "sub-select" approach takes about 4 seconds. To make it fast, I added a sorted index on _Notes that included ParentId, CreatedOn (Sorted descending) and the Display column was included). This drops the query to less than 1 second on 47k records.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453707

For some data distributions this approach could 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

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166486

Have a look at using ROW_NUMBER

Something like

;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

Upvotes: 4

ta.speot.is
ta.speot.is

Reputation: 27214

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

Upvotes: 0

Related Questions