SELECT   Count(* ),
         domain.id,
         domain.href,
         Count(DISTINCT urlcontact.contact_id) AS contact_count,
         USER.id,
         USER.first_name,
         USER.last_name,
         (SELECT   Min(agenturl2.id)
          FROM     agent_urls agenturl2
          WHERE    agenturl2.agent_id = 377
          GROUP BY agenturl2.agent_id) AS discoveryorder
FROM     domains domain
         LEFT OUTER JOIN project_domains projectdomain
           ON domain.id = projectdomain.domain_id
         LEFT OUTER JOIN users USER
           ON USER.id = projectdomain.assigned_to_user_id,
         contacts contact,
         url_contacts urlcontact,
         urls url,
         agent_urls agenturl,
         agents agent
WHERE    url.domain_id = domain.id
         AND urlcontact.contact_id = contact.id
         AND contact.dead = 0
         AND urlcontact.url_id = url.id
         AND agenturl.agent_id = 377
         AND agenturl.url_id = url.id
         AND agent.id = agenturl.agent_id
         AND agent.project_id = 11
         AND domain.id IN (2,3,4,5,
                           6,7,8,9,
                           10,11,12,13,
                           14,15,16,17,
                           18,19,21,22,
                           23,24,25,26,
                           27,28,29,30,
                           31,32,33,34,
                           35,36,37,38,
                           39,40,41,42,
                           43,44,45,46,
                           47,48,49,50,
                           51,52,53,54,
                           55,56,57,58,
                           59,61,62,63,
                           64,65,66,67,
                           68,69,70,72,
                           73,74,75,76,
                           77,78,79,80,
                           81,82,83,84,
                           85,86,87,88,
                           89,90,91,92,
                           94,95,97,98,
                           99,100,101,102,
                           103,105,107,108)
ORDER BY discoveryorder ASC,
         contact_count DESC
LIMIT    0,100