WITH total AS
(
       SELECT rang,
              nom_complet,
              CASE
                     WHEN cd_nom = cd_ref THEN Concat (lb_nom,' ','<b><span style="color:#02138e">(nom valide)</span></b>')
                     ELSE Concat( lb_nom,' ', '<b><span style="color:red"> (syn. de ',nom_valide,')','</span></b>')
              END                          AS lb_nom_key,
              cd_nom                       AS cd_nom_key,
              Concat_ws('!',lb_nom,cd_nom) AS lb_cd_nom_key,
              regne,
              group2_inpn AS groupe,
              group1_inpn,
              fr,
              lower(
              CASE
                     WHEN (
                                   lb_nom ilike '% f. %'
                            OR     lb_nom ilike '%(%'
                            OR     lb_nom ilike '% x %'
                            OR     lb_nom ilike '%var.%') THEN NULL::text
                     ELSE trim(concat(LEFT(split_part(lb_nom,' ',1),3),' ',LEFT(split_part(lb_nom,' ',2),3),' ',LEFT(split_part(lb_nom,' ',3),3)))
              END) AS code_espece_key
       FROM   inpn.taxref_v12
       UNION
       SELECT rang,
              nom_complet,
              CASE
                     WHEN nom_vern IN
                            (
                                     SELECT   nom_vern
                                     FROM     inpn.taxref_v12
                                     WHERE    rang = 'ES'
                                     GROUP BY nom_vern
                                     HAVING   count (DISTINCT cd_ref) > 1) THEN concat(nom_vern,' ','<b><span style="color:#ff7e06">(',lb_nom,')</span></b>')
                     ELSE concat(nom_vern,' -> ',lb_nom,' ','<b><span style="color:#02138e">(nom valide)</span></b>')
              END                          AS lb_nom_key,
              cd_nom                       AS cd_nom_key,
              concat_ws('!',lb_nom,cd_nom) AS lb_cd_nom_key,
              regne,
              group2_inpn AS groupe,
              group1_inpn,
              fr,
              lower(
              CASE
                     WHEN (
                                   lb_nom ilike '% f. %'
                            OR     lb_nom ilike '%(%'
                            OR     lb_nom ilike '% x %'
                            OR     lb_nom ilike '%var.%') THEN NULL::text
                     ELSE trim(concat(LEFT(split_part(lb_nom,' ',1),3),' ',LEFT(split_part(lb_nom,' ',2),3),' ',LEFT(split_part(lb_nom,' ',3),3)))
              END) AS code_espece_key
       FROM   inpn.taxref_v12
       WHERE  cd_nom = cd_ref
       AND    nom_vern IS NOT NULL
       AND    rang = 'ES' )
SELECT   lb_nom_key,
         cd_nom_key,
         lb_cd_nom_key,
         regne AS regne_key,
         groupe,
         group1_inpn,
         code_espece_key ,
         rank() OVER(ORDER BY
         CASE
                  WHEN rang ='GN' THEN 1
                  ELSE 2
         END,
         CASE
                  WHEN lb_nom_key ilike '%nom valide%' THEN 3
                  ELSE 4
         END, lb_nom_key ) AS sortby
FROM     total
WHERE    regne IN ('Animalia')
AND      (
                  fr = 'P'
         OR       fr ='E'
         OR       fr ='S'
         OR       fr ='C')
AND      (
                  groupe NOT IN ('Diatomées' ,
                                 'Scléractiniaires' ,
                                 'Pycnogonides' ,
                                 'Octocoralliaires' ,
                                 'Ochrophyta' ,
                                 'Rhodophytes' ,
                                 'Némertes' ,
                                 'Nématodes',
                                 'Hydrozoaires' ,
                                 'Ascidies' ,
                                 'Annélides',
                                 'Acanthocéphales')
         OR       groupe IS NULL )
AND      (
                  group1_inpn NOT IN ('Bryozoaires',
                                      'Cnidaires',
                                      'Cryptophytes',
                                      'Cténaires',
                                      'Cyanobactéries',
                                      'Echinodermes',
                                      'Foraminifères',
                                      'Gastrotriches',
                                      'Onychophores',
                                      'Porifères',
                                      'Rotifères',
                                      'Siponcles',
                                      'Tardigrades',
                                      'Vers',
                                      'Protéobactéries')
         OR       group1_inpn IS NULL)
ORDER BY sortby