Kutubs sent me this query back in May. Turns out it was useful enough to use again today having specifically looked for it in Outlook, so let's give some Kutub credit where Kutub credit is due....
Dear Mr. Sql -----,
If you needed to query Northwind (with a gun to your head) and get a list of all customers joined with the orderid of only the last order they made, and if an order doesn't exist the orderid should be null so that all customers come back (outer join)...how would you do that? Is this a kutub query or am I missing something simple? Should I just go home? I'd rather write the query though.
simple,
this query will return the last order for each customer from the order table
select a.orderID
from orders a
where orderID = (select top 1 orderID from orders b where b.customerID = a.customerID order by orderDate desc)
now we need to KUTUB it so we get all the customers that do not have any orders, so we add another select from customer to it and make it a column query
select c.customerID,
(select a.orderID
from orders a
where orderID = (select top 1 orderID from orders b where b.customerID = a.customerID order by orderDate desc)
and c.customerID = a.customerID) as lastorderID
from customers c
order by c.customerId
