title: mysql union all not working
body: <p>Using the MySQL code below along with ORDER BY is not working. I've searched and tried various version of the code (UNION, UNION ALL, with brackets, without brackets, a SELECT wrapping all the SELECT UNION ALLs then sorting that) - no luck. Any other suggestions?</p>
  
  <pre><code>        (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='res' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM res_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='con' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM con_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='mul' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM mul_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='rnt' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM rnt_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='ici' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM ici_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='far' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM far_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      UNION ALL
  
      (SELECT FORMAT(lt.`rets_111`, 0) AS 'ListPrice', lt.`rets_128` AS 'MlsNumber', lt.`rets_13` AS 'Address', lt.`rets_2044` AS 'PublicRemarks', lt.`rets_sysid`, @real_estate_type:='lot' AS real_estate_type,
      lt.`rets_2362` AS 'OpenHouseDate1', lt.`rets_2364` AS 'OpenHouseStartTime1', lt.`rets_2366` AS 'OpenHouseEndTime1', lt.`rets_2370` AS 'DisplayOpenHouse1',
      lt.`rets_2372` AS 'OpenHouseDate2', lt.`rets_2374` AS 'OpenHouseStartTime2', lt.`rets_2376` AS 'OpenHouseEndTime2', lt.`rets_2380` AS 'DisplayOpenHouse2'
      FROM lot_listings AS lt
      WHERE lt.`rets_32` IN ('AGENT1', 'AGENT2') OR lt.`rets_2337` IN ('AGENT1', 'AGENT2') OR lt.`rets_1836` IN ('AGENT1', 'AGENT2'))
  
      ORDER BY 'ListPrice' DESC
  </code></pre>