SELECT id, amount, reg_id, reg_name, status_id, description, status_name,
 trigram, mreg_prod_id, order_number, tariff_id, tariff_name, personality, tariff_code, tariff_tech_name,
 vendor_id, vendor_name, hlr_id, hlr_value, profile_id, profile_name, mr_id, mr_name,  auth_alg_name,
 MAX(code_code) AS code_code, MAX(card_type_id) AS card_type_id, MAX(card_type_name) AS card_type_name
FROM (
SELECT pr.id, pr.amount, r.id AS reg_id, r.name AS reg_name, pr.status_id, pr.description, s.name AS status_name
, r.trigram, pm.id AS mreg_prod_id, pm.order_number AS order_number,
tf.id AS tariff_id, tf.name AS tariff_name, tf.personality AS personality, tf.code AS tariff_code, tf.tech_name AS tariff_tech_name,
v.id AS vendor_id, v.name AS vendor_name, hlr.id AS hlr_id, hlr.VALUE AS hlr_value,
p.id AS profile_id, p.name AS profile_name, mr.id AS mr_id, mr.name AS mr_name, cd.code AS code_code,
a.name AS auth_alg_name, d.id AS card_type_id, d.name AS card_type_name, p.code AS profile_code
FROM usim_production_region pr
JOIN usim_region r ON r.id = pr.reg_id
JOIN usim_production_macroregion pm ON pm.id = pr.mreg_prod_id
JOIN usim_status s ON s.id = pr.status_id
JOIN usim_vendor v ON v.id = pm.vendor_id
JOIN usim_profile p ON p.id = pr.profile_id
LEFT JOIN usim_auth_algorythm a ON p.auth_algo_id=a.id
JOIN usim_hlr hlr ON hlr.id = pr.hlr_id
JOIN usim_macro_region mr ON mr.id = pm.mreg_id
JOIN usim_tariff tf ON tf.id = pm.tariff_id
LEFT JOIN usim_code_macro_region cm ON cm.mreg_id = mr.id
LEFT JOIN usim_code cd ON cd.id = cm.code_id AND cd.tariff_id = tf.id AND cd.card_type_id = pm.card_type_id
LEFT JOIN usim_card_type d ON d.id = cd.card_type_id)
GROUP BY id, amount, reg_id, reg_name, status_id, description, status_name,
 trigram, mreg_prod_id, order_number, tariff_id, tariff_name, personality, tariff_code, tariff_tech_name,
 vendor_id, vendor_name, hlr_id, hlr_value, profile_id, profile_name, mr_id, mr_name,  auth_alg_name,
 profile_code;


WITH t AS (SELECT pr.id, pr.amount, r.id AS reg_id, r.name AS reg_name, pr.status_id, pr.description, s.name AS status_name
, r.trigram, pm.id AS mreg_prod_id, pm.order_number AS order_number,
tf.id AS tariff_id, tf.name AS tariff_name, tf.personality AS personality, tf.code AS tariff_code, tf.tech_name AS tariff_tech_name,
v.id AS vendor_id, v.name AS vendor_name, hlr.id AS hlr_id, hlr.VALUE AS hlr_value,
p.id AS profile_id, p.name AS profile_name, mr.id AS mr_id, mr.name AS mr_name, cd.code AS code_code,
a.name AS auth_alg_name, d.id AS card_type_id, d.name AS card_type_name, p.code AS profile_code
FROM usim_production_region pr
JOIN usim_region r ON r.id = pr.reg_id
JOIN usim_production_macroregion pm ON pm.id = pr.mreg_prod_id
JOIN usim_status s ON s.id = pr.status_id
JOIN usim_vendor v ON v.id = pm.vendor_id
JOIN usim_profile p ON p.id = pr.profile_id
LEFT JOIN usim_auth_algorythm a ON p.auth_algo_id=a.id
JOIN usim_hlr hlr ON hlr.id = pr.hlr_id
JOIN usim_macro_region mr ON mr.id = pm.mreg_id
JOIN usim_tariff tf ON tf.id = pm.tariff_id
LEFT JOIN usim_code_macro_region cm ON cm.mreg_id = mr.id
LEFT JOIN usim_code cd ON cd.id = cm.code_id AND cd.tariff_id = tf.id AND cd.card_type_id = pm.card_type_id
LEFT JOIN usim_card_type d ON d.id = cd.card_type_id
WHERE (pr.status_id IN (35,48,38,55,39,36,37,40,47))
)
SELECT * FROM t WHERE code_code IS NOT NULL
UNION ALL
(SELECT * FROM t WHERE code_code IS NULL AND);