SQL TOP 1 returning null when used in a sub-query

Question

Please can you help with this sub query - it works fine I run it by itself for one client but returns null when added to the query.

For each client I need to get the employee with the most orders.

This is part of a much bigger query so performance is also an issue.

SELECT c.ClientId, s.BestEmployeeId 
FROM client c
LEFT OUTER JOIN(
SELECT TOP 1 o.EmployeeId AS BestEmployeeId, count(o.EmployeeId) AS cnt, o.ClientId
    FROM Orders o
    WHERE o.EmployeeId > 0
    AND o.EmployeeId is not null    
    GROUP BY o.ClientId, o.EmployeeId
    ORDER BY cnt DESC
) AS s on c.ClientId = s.ClientId

Answer 1

For each client I need to get the employee with the most orders.

I would do this with aggregation and window functions:

select oc.*
from (select o.client, o.employeeid, count(*) as cnt,
             rank() over (partition by o.client order by count(*) desc) as seqnum
      from Orders o
      group by o.client, o.employeeid
     ) oc
where seqnum = 1;

Note that you don't seem to need the client table, because orders appears to have both client and employeeid.

Your version is filtering out NULL values of employeeid. You can do this in the subquery as well (although your problem statement says nothing about such values).