SELECT r.*, g.id as id_groupe, g.nom as nom_groupe, g.image as image_groupe, g.estvisible as estVisible_groupe, g.adressePage as adressePage_groupe FROM residences r LEFT JOIN groupes_residences g ON r.groupe = g.id WHERE typeClientele REGEXP '[[:<:]]1[[.,.]]' AND typeClientele REGEXP '([[:<:]]1[[.,.]])' AND r.id != 2293 AND r.id != 2292 AND enActivite = 1 GROUP BY r.id ORDER BY (SELECT COUNT(*) FROM utilisateurs u WHERE u.res_id = r.id AND u.relation = 'Client') DESC, RAND(178993) LIMIT 420,15