News for hackers. Stuff that matters.
Re:Learn SQL, dammit by chris_mahan on Wednesday May 08, 2013 @03:14PM (#5676221)
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.

Re:Learn SQL, dammit by pfg on Wednesday May 08, 2013 @04:30PM (#5676711)
You 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!

Re:Learn SQL, dammit by chris_mahan on Wednesday May 08, 2013 @06:00PM (#5677235)
Sure. 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)