SELECT S.sp_code as the_code, MAX(S.Genus) as Genus, MAX(S.Species) as Species, MAX(S.Rank) as Rank, MAX(S.Rank_epithet) as Rank_epithet, MAX(S.Sub_pop) as Sub_pop, GROUP_CONCAT(CC.Common_name ORDER BY CC.Common_name ASC SEPARATOR ', ') as nomi_comuni, MAX(RL.rl_category) as rl_category, MAX(RL.rl_criteria) as rl_criteria , MAX(RL.rl_trend) as rl_trend FROM iucn_redlist.Systematics S JOIN iucn_redlist.red_listing RL ON (S.sp_code=RL.sp_code) LEFT OUTER JOIN iucn_redlist.common_names CC ON (S.sp_code=CC.sp_code) WHERE RL.rl_category!='NE' AND S.gruppo_nr!='9' AND RL.rl_category="RE" GROUP BY S.sp_code ORDER BY S.Genus, S.Species, S.Rank, S.Rank_epithet, S.Sub_pop