Also easy to do this with a lateral join (and a limit 1 in the subquery).
The nice thing about lateral joins is that they're very easy to understand conceptually once you get over the weird syntax.
For each row in my query so far, do this other thing.
Since I learned them, everything went from being a puzzle on its own, "I know what I want to do but how to express it in SQL" -- to simply being writing things out naturally..
We're using such subqueries a lot, often for many columns in the same child table, and we're moving to MSSQL so will definitely change to using lateral join (or cross apply as MSSQL calls it).
It would be nice to have this feature; its a handy hammer for the right type of nail.