i'm new to SOQL and SF, so bear with me :) I have Sales_Manager__c object tied to Rent__c via Master-Detail relationship. What i need is to get Manager with highest number of Rent deals for a current year. I figured out that the Rents__r.size column stores that info. The question is how can i gain access to the size column and retrieve highest number out of it? Here is the picture of query i have
My SOQL code
SELECT (SELECT Id FROM Rents__r WHERE StartDate__c=THIS_YEAR) FROM Sales_Manager__c
Try other way around, by starting the query from Rents and then going "up". This will let you sort by the count and stop after say top 5 men (your way would give you list of all managers, some with rents, some without and then what, you'd have to manually go through the list and find the max).
SELECT Manager__c, Manager__r.Name, COUNT(Id)
FROM Rent__c
WHERE StartDate__c=THIS_YEAR
GROUP BY Manager__c, Manager__r.Name
ORDER BY COUNT(Id) DESC
LIMIT 10
(I'm not sure if Manager__c is the right name for your field, experiment a bit)