CODEWITHSUNDEEP
sqlsql-servert-sqldategreatest-n-per-group
GregH
GregH

Reputation: 5459

SQL: Selecting columns from most recent child table entry

I'm currently trying to find a way to retrieve all records in a table with their most recent associated child record. A simplified table structure would look as follows:

Parents        Children
______         ________
Id             Id
Age            ParentId 
               FirstName
               LastName
               DateCreated

I'm looking to get all parents with the first and last name of their most recently created child (ordered by DateCreated). These tables are very large in practice and I'm hoping to gain some performance over selecting all parents and all their associated children which is what I currently have via the following query:

select * from parents p join children c on p.id = c.parentid

and then finding the desired results with application code in memory.

All help is appreciated.

Upvotes: 0

Views: 255

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

If I understand correctly, you can use a lateral join (i.e. apply):

select p.*, c.*
from parents p cross apply
     (select top (1) c.*
      from children c 
      where p.id = c.parentid
      order by c.datecreated desc
     ) c;

For optimal performance, you want an index on children(parentid, datecreated desc).

Upvotes: 1

GMB
GMB

Reputation: 222622

You can filter with a subquery:

select p.*, c.firstname, c.lastname
from parents p
inner join children c on c.parentid = p.id
where c.datecreated = (
    select max(c1.datecreated)
    from children c1
    where c1.parentid = c.parentid
) c

Another option is a lateral join:

select p.*, c.firstname, c.lastname
from parents p
cross apply (
    select top (1) c.*
    from children c
    where c.parentid = p.id
    order by c.datecreated desc
)

Upvotes: 1

Related Questions