SELECT SQL_NO_CACHE *
FROM (
         SELECT
           tn.id                       AS id,
           tn.fullnumber               AS full_number,
           tn.npa                      AS npa,
           tn.nxx                      AS nxx,
           tn.block                    AS block,
           tn.tn_status                AS status,
           tn.account_id               AS account_id,
           tn.sippeer_id               AS sippeer_id,
           nbr.city                    AS city,
           nbr.rc_state                AS state,
           nbr.rc_abbreviation         AS rate_center,
           nbr.rc_lata                 AS lata,
           t.tier                      AS tier,
           v.id                        AS vendor_id,
           v.name                      AS vendor_name,
           tn.last_modified            AS last_modified,
           le.subscriber               AS lidb_subscriber,
           le.type                     AS lidb_type,
           le.visibility               AS lidb_visibility,
           lrr.id                      AS current_lidb_request,
           e911tnr.request_status      AS e911_request_status,
           de.subscriber_type          AS subscriber_type,
           de.listing_type             AS listing_type,
           dln.firstname               AS firstname,
           dln.firstname2              AS firstname2,
           dln.lastname                AS lastname,
           dln.designation             AS designation,
           dln.title_of_lineage        AS title_of_lineage,
           dln.title_of_address        AS title_of_address,
           dln.title_of_address2       AS title_of_address2,
           dln.title_of_lineage_name2  AS title_of_lineage_name2,
           dln.title_of_address_name2  AS title_of_address_name2,
           dln.title_of_address2_name2 AS title_of_address2_name2,
           dln.place_listing_as        AS place_listing_as,
           de.list_address             AS list_address,
           a.address_type              AS address_type,
           a.house_prefix              AS house_prefix,
           a.house_no                  AS house_no,
           a.house_suffix              AS house_suffix,
           a.pre_directional           AS pre_directional,
           a.street_name               AS street_name,
           a.street_suffix             AS street_suffix,
           a.post_directional          AS post_directional,
           a.address_line_2            AS address_line_2,
           a.city                      AS dlda_city,
           r.abbreviation              AS state_code,
           a.zip                       AS zip,
           a.zip_4                     AS zip_4,
           a.county                    AS county,
           drr.id                      AS dlda_request_record_id,
           dtx.id                      AS dlda_tn_xref_id,
           tor.order_id                AS pending_dlda_order_id,
           dod.order_status            AS pending_dlda_order_status
         FROM (
         (SELECT tn.id id
        FROM
          telephonenumber AS tn INNER JOIN profile_network AS pn ON tn.fullnumber = pn.id
          INNER JOIN profiles AS p
            ON p.profile_id = pn.acc_profile AND p.name LIKE '%destination_host%' AND p.value = '107.20.160.82'
       )
        UNION
        (SELECT tn.id id FROM telephonenumber AS tn
        INNER JOIN (
        SELECT stgx.sippeer_id id FROM sippeer_term_gateway_xref AS stgx
        INNER JOIN profile_ips AS pips ON stgx.gateway=pips.idprofile_ips AND stgx.mask=pips.mask AND stgx.port=pips.port
        WHERE stgx.gateway='107.20.160.82' AND stgx.mask=32
        ) AS sip ON sip.id=tn.sippeer_id)) AS host
  JOIN telephonenumber AS tn ON tn.id = host.id
  JOIN vendor AS v ON v.id = tn.vendor_id
  LEFT JOIN npanxx_block_ratecenter AS nbr ON nbr.npa = tn.npa AND nbr.nxx = tn.nxx AND nbr.block = tn.block
  LEFT JOIN ratecenter_tier_xref AS rt
    ON rt.vendor_id = tn.vendor_id AND rt.rc_abbreviation = nbr.rc_abbreviation AND rt.rc_state = nbr.rc_state
  LEFT JOIN tier AS t ON t.id = rt.tier_id
  LEFT JOIN lidb_tn_xref AS ltx ON ltx.tn = tn.fullnumber
  LEFT JOIN lidb_entry AS le ON le.id = ltx.lidb_entry_id
  LEFT JOIN lidb_request_record AS lrr ON lrr.tn = tn.fullnumber
  LEFT JOIN e911_telephone_number_request AS e911tnr ON e911tnr.fullnumber = tn.fullnumber
  LEFT JOIN dlda_tn_xref AS dtx ON dtx.tn = tn.fullnumber
  LEFT JOIN dlda_entry AS de ON de.id = dtx.dlda_entry_id
  LEFT JOIN dlda_listing_name AS dln ON dln.id = de.listing_name_id
  LEFT JOIN dlda_request_record AS drr ON drr.tn = tn.fullnumber
  LEFT JOIN dlda_request_order_xref AS drox ON drox.id = drr.dlda_request_order_xref_id
  LEFT JOIN dlda_order_detail AS dod ON dod.typed_order_id = drox.typed_order_id
  LEFT JOIN typed_order AS tor ON tor.id = drox.typed_order_id
  LEFT JOIN address AS a ON a.id = de.address_id
  LEFT JOIN region AS r ON r.id = a.region_id
  LIMIT 251
) as sel1
GROUP BY sel1.id
ORDER BY sel1.full_number;