Query that merges latest reference in multiple tables into shared rows

I have the following schema which is pretty simple and straight forward:

I want to write an optimized query that returns me a list of all phones with their latest message and latest picture taken. In this case with the latest “CreatedAt” for both fields:

Example expected data-set:

-------------------------------------------
|  Phone Id   |  Message Id |  Picture Id |
-------------------------------------------
|  1          |      3      |      4      |
|  2          |      4      |      5      |
|  3          |      5      |      6      |
-------------------------------------------

Right now I’m not sure how to write such a query so I just grab everything and then programatically filter it out with server side code i.e:

SELECT * FROM Phones
LEFT OUTER JOIN Messages ON Messages.PhoneId = Phones.Id
LEFT OUTER JOIN Photos ON Photos.PhoneId = Phones.Id

--and then code that filters the CreatedAt in another language

How can I write the following query?

Answer

OUTER APPLY seems useful here:

SELECT p.*, m.*, ph.*
FROM Phones p OUTER APPLY
     (SELECT TOP (1) m.*
      FROM Messages m
      WHERE m.PhoneId = p.Id
      ORDER BY m.CreatedAt DESC
     ) m OUTER APPLY
     (SELECT TOP (1) ph.*
      FROM Photos ph
      WHERE ph.PhoneId = p.Id
      ORDER BY ph.CreatedAt DESC
     ) ph;