title: Large Complex Query with UNION allows me to ORDER BY but not GROUP - why?
body: <p>I have a large, complex query that combines multiple queries into a single output by using UNION.</p>
  
  <p>It's essentially this:</p>
  
  <pre><code>SELECT *
  FROM (SELECT query1.field1,
               subquery.field1,
               GROUP_CONCAT(DISTINCT subquery.field1 SEPARATOR ', ') AS 'Column3',
               NULL AS 'Column4'
        FROM (SELECT table1.field1, table1.field2, subquery1.field1
              FROM table1
              INNER JOIN (SELECT table2.field1
                          FROM table2) AS subquery1 ON table1.field1 = subquery.field1)
        GROUP BY subquery.field1) AS query1
  UNION 
  SELECT *
  FROM (SELECT query1.field1,
               subquery.field1,
               GROUP_CONCAT(DISTINCT subquery.field1 SEPARATOR ', ') AS 'Column3',
               NULL AS 'Column4'
        FROM (SELECT table1.field1, table1.field2, subquery1.field1
              FROM table1
              INNER JOIN (SELECT table2.field1
                          FROM table2) AS subquery1 ON table1.field1 = subquery.field1)
        GROUP BY subquery.field1) AS query2
  ORDER BY field1
  </code></pre>
  
  <p>This is very very overly simplified (there are upwards of 15 subqueries in the whole thing). It IS returning the results set I want. If I split out each group and run the code for just "query1" or "query2", I get the correct results. The problem I'm running into is when I UNION them into a single results set.</p>
  
  <p>Essentially, I want a set returned for Query1 and a set returned for Query2. But I want these grouped by person (the identifier is field1), so that each row belongs to only a single person and displays information in the columns for both query 1 AND query 2.</p>
  
  <p>What I'm getting right now is the list of people, and if they meet both criteria, they get 2 lines. One for appearing in query1 and one for appearing in query2. Some people will appear in both, some people will only appear in one or the other.</p>
  
  <p>I <em>want</em> to see:</p>
  
  <pre><code>column 1 | column 2 | column 3 | column 4
  field 1  | field 2  | field 3  | field 4
  </code></pre>
  
  <p>What I'm getting instead is:</p>
  
  <pre><code>column 1 | column 2 | column 3 | column 4
  field 1  | field 2  | field 3  | NULL
  field 1  | field 2  | NULL     | field 4
  </code></pre>
  
  <p>I can ORDER BY field1 so that it places these two lines together, but if I GROUP BY field1, I get a syntax error for unknown column.</p>
  
  <p>I don't understand how it's a known column for ORDER BY but unknown for GROUP BY.</p>