SELECT
    t.Name,
    t.Class
FROM (
SELECT
    FirstName+' '+ LastName AS Name,
    m.Class,
    COUNT(m.Class) COUNT,
    DENSE_RANK() OVER(PARTITION BY FirstName+' '+ LastName ORDER BY COUNT(m.Class) DESC) AS Rank
FROM Clients AS c
LEFT JOIN Orders AS o ON o.ClientId = c.Id
LEFT JOIN Vehicles AS v ON v.Id = o.VehicleId
LEFT JOIN Models AS m ON m.Id = v.ModelId
GROUP BY FirstName+' '+ LastName,m.Class) as t
WHERE t.Rank = 1 AND t.Class IS NOT NULL
ORDER BY t.Name,t.Class