SELECT * FROM ((SELECT COUNT(id_offre) AS nb, id, nom, position FROM ((SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position FROM offre o, agence a, departement d, departement_offre do WHERE a.id_agence=o.id_agence AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 AND (o.id_secteur=28) ) UNION (SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position FROM offre o, entreprise e, departement d, departement_offre do WHERE e.id_entreprise=o.id_entreprise AND o.id_flux=0 AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 AND (o.id_secteur=28) ) UNION (SELECT o.id_offre, do.id_departement AS id, concat(d.code,' - ',d.nom) AS nom, 0 AS position FROM offre o, recruteur_xml r, departement d, departement_offre do WHERE r.id_recruteur=o.id_entreprise AND o.id_flux>0 AND o.id_offre=do.id_offre AND do.id_departement=d.id_departement AND o.actif=1 AND (o.id_secteur=28) )) t1 GROUP BY id) UNION (SELECT 0 AS nb, d.id_departement AS id, concat(d.code,' - ',d.nom), 0 AS position FROM departement d) ORDER BY nb DESC, nom ASC) t2 GROUP BY id ORDER BY nb DESC, nom ASC