query2.sql

WITH table_join AS (
    SELECT rowdex, "value", field_id FROM cucm_csv_batrepeatingfield
    WHERE batdata_id = 10 AND role = 'phone' AND
          ((field_id = 327 AND value ILIKE '%US%') OR
          (field_id = 295 AND value ILIKE '%10000%') OR
          (field_id = 220 AND value ILIKE '%Cu1%'))),
sort_join AS (
    SELECT s0.rowdex, "Directory Number", "Route Partition" FROM
        (SELECT DISTINCT table_join.rowdex, table_join.value as "Directory Number" FROM table_join WHERE table_join.field_id = 295) AS s0 FULL OUTER JOIN
        (SELECT DISTINCT table_join.rowdex, table_join.value as "Route Partition" FROM table_join WHERE table_join.field_id = 220) AS s1 ON s0.rowdex = s1.rowdex)

SELECT f0.rowdex, "Directory Number", "Route Partition" FROM
    (SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 327) AS f0 INNER JOIN
    (SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 295) AS f1 ON f0.rowdex = f1.rowdex
    INNER JOIN
    (SELECT DISTINCT table_join.rowdex FROM table_join WHERE table_join.field_id = 220) AS f2 ON f0.rowdex = f2.rowdex LEFT JOIN
    sort_join ON f0.rowdex = sort_join.rowdex
ORDER BY "Directory Number" ASC NULLS FIRST, "Route Partition" DESC NULLS LAST;