Compute an aggregate once and join it back.
Section: subqueries and ctes
Join to a derived table
sql
sql
SELECT u.id, u.email, x.last_order_at
FROM users u
LEFT JOIN (
SELECT user_id, MAX(created_at) AS last_order_at
FROM orders
GROUP BY user_id
) x ON x.user_id = u.id;Explanation
Derived tables are handy when you want one summarized row per parent entity.
Learn the surrounding workflow
Compare similar commands or jump into common fixes when this command is part of a bigger troubleshooting path.
Related commands
Same sheet · prioritizing subqueries and ctes
Use a CTE to isolate recent orders
Create a readable query pipeline with `WITH`.
Left join to keep unmatched parent rows
Return all rows from the left table and matching rows from the right table.