3 ms·
You can use CROSS APPLY on tsql, and it allows you to use an order by in the inner query, for example, if you wanted to know the id of the employee's latest ass
by chris_mahan 13y ago
You can use CROSS APPLY on tsql, and it allows you to use an order by in the inner query, for example, if you wanted to know the id of the employee's latest assignment.
SELECT e.*
, assignment_id = b.id
FROM employees e
CROSS APPLY (SELECT top 1 a.id
FROM assignments a
WHERE a.employee_id = e.id
ORDER BY a.assignment_date DESC
) b
In tsql, you are not allowed to have order by in a subquery (if memory serves me right)
Edit: Oh crap, I forgot this isn't SO, and the formatting went out the window.
- pfg 13y agoYou can use ORDER BY in connection with TOP n, for example if you want to use the result of a subquery as a column: SELECT user_name, (SELECT TOP 1 last_action FROM actions WHERE actions.user_id=users.user_id ORDER BY action_timestamp) FROM users (TOP is T-SQLs version of LIMIT) Also, thanks for making me aware of CROSS APPLY, haven't seen that before!
- chris_mahan 13y agoSure. Note also that CROSS APPLY allows you to retrieve multiple columns, unlike an in-SELECT subquery (unless you want to have that query written multiple times)