SELECT * FROM ((SELECT COUNT(id_offre) AS nb, id, nom, position FROM ((SELECT o.id_offre, co.id_type_contrat AS id, cc.nom AS nom, cc.position FROM offre o, agence a, config_type_contrat cc, offre_type_contrat co WHERE a.id_agence=o.id_agence AND o.actif=1 AND o.id_offre=co.id_offre AND co.id_type_contrat=cc.id_type_contrat AND ((LOWER(o.titre) LIKE '%secretaire%' OR LOWER(o.mots_cles) LIKE '%secretaire%' OR LOWER(o.description) LIKE '%secretaire%') AND (LOWER(o.titre) LIKE '%medicale%' OR LOWER(o.mots_cles) LIKE '%medicale%' OR LOWER(o.description) LIKE '%medicale%')) ) UNION (SELECT o.id_offre, co.id_type_contrat AS id, cc.nom AS nom, cc.position FROM offre o, entreprise e, config_type_contrat cc, offre_type_contrat co WHERE e.id_entreprise=o.id_entreprise AND o.id_flux=0 AND o.actif=1 AND o.id_offre=co.id_offre AND co.id_type_contrat=cc.id_type_contrat AND ((LOWER(o.titre) LIKE '%secretaire%' OR LOWER(o.mots_cles) LIKE '%secretaire%' OR LOWER(o.description) LIKE '%secretaire%') AND (LOWER(o.titre) LIKE '%medicale%' OR LOWER(o.mots_cles) LIKE '%medicale%' OR LOWER(o.description) LIKE '%medicale%')) )) t1 GROUP BY id) UNION (SELECT 0 AS nb, cc.id_type_contrat AS id, cc.nom, cc.position FROM config_type_contrat cc) ORDER BY nb DESC, position ASC) t2 GROUP BY id ORDER BY nb DESC, position ASC