SOQL help with subquery where clause
SELECT Id, Name, CreatedDate, (SELECT Name FROM Status_History__r ORDER BY CreatedDate DESC LIMIT 1)
FROM Service_Order__c WHERE id IN (SELECT Service_Order__c FROM Status_History__c WHERE Name = 'Order Completed')
The above should work I believe and worth trying
In response to your update, the child relationship is normally called something like Status_Histories__r.
If you go in the UI to custom objects, look at the object Status History, look at the lookup field that it has to Service Order, then you will see the correct child relationship name there.
You should have
WHERE ID IN
rather than
WHERE ID =