Now, I've got a pretty huge query that needs to use a correlated subquery when the outer query has a GROUP BY clause. Here's an extract (I've missed out 'simple' fields which come straight out of a table):
MAX(o.order_date) as lastOrderDate,
(SELECT order_channel_id FROM [ReportsED].[dbo].order_ ord WHERE ord.bill_to_contact_id = c.contact_id AND ord.order_date = o.order_date) as lastOrderChannelId
FROM [ReportsED].[dbo].Contact c
LEFT OUTER JOIN [ReportsED].[dbo].Order_ o ON o.bill_to_contact_id = c.contact_id
CHARINDEX(left(c.Zip, 1), 'ABCDEFGHIJKLMNOPQRSTUVWXYZ') > 0
[snip rest of fields corresponding to those that aren't aggregates]
When running this I get "The query processor encountered an unexpected error during execution", as explained by the bug.
Now, the Microsoft article recommends using DISTINCT to eliminate the GROUP BY clause. How do I do that in this more complex scenario where I not only already have a DISTINCT in the SELECT, but also have multiple GROUP BY fields?
You need to get that select statement out of your field list. Here is one way, using nested queries. Try running each level of the query separately to see how the inner queries return the channel_id for the most recent order date. You might be able to eliminate the outermost layer by using an aggregate query instead.
left outer join
(select bill_to_contact_id. Max(order_.order_date) as order_date from order_) lastOrderDates
on order_.bill_to_contact_id = lastOrderDates.bill_to_contact_id
and order_.order_date = lastOrderDates.order_date) channel_ids
on Contact.contact_id = channel_ids.bill_to_contact