title: MySQL get first non null value after group by
body: <p>I have a large table with data that is not unique but needs to be. This table is a result of multiple union selects so is not an actual table. I cannot make it an actual table for other reasons.</p>
  <p>All of the UNION'd tables have an email column which will eventually be unique. The resulting records look like this:</p>
  <pre><code>1   ozzy@test.com   Ozzy
  2   test@test.com   Tony
  3   test@yahoo.com  Steve
  4   tiny@test.com   
  13  tony@gmail.com  Tony
  14  test@test.com   Ozzy
  15  test@yahoo.com  Dave
  16  tiny@test.com   Tim
  </code></pre>
  <p>As you can see, some emails appear more then once with different names or non-existent names. When I add a <code>GROUP BY email</code> clause at the end, the results look like this:</p>
  <pre><code>1   ozzy@test.com   Ozzy
  2   test@test.com   Tony
  3   test@yahoo.com  Steve
  4   tiny@test.com   
  13  tony@gmail.com  Tony
  </code></pre>
  <p>As you can see, email 4 does not have a name because it chose the first entry with <code>NULL</code> for a name. Then I tried to use <code>GROUP_CONCAT</code> which made the results look like this:</p>
  <pre><code>1   ozzy@test.com   Ozzy
  14  test@test.com   Ozzy,Tony
  15  test@yahoo.com  Dave,Steve
  16  tiny@test.com   Tim
  13  tony@gmail.com  Tony
  </code></pre>
  <p>As you can see, now everyone has a name but some rows have more then one name concatenated. What I want to do is <code>GROUP BY email</code> and choose the first <code>NOT NULL</code> entry of each column for each row to theoretically look like so:</p>
  <pre><code>1   ozzy@test.com   Ozzy
  2   test@test.com   Tony
  3   test@yahoo.com  Steve
  4   tiny@test.com   Tim
  13  tony@gmail.com  Tony
  </code></pre>
  <p>I have tried using <code>COALESCE</code> but it doesn't work as intended. My current query looks like so:</p>
  <pre><code>SELECT
      id,
      email,
      `name`
  FROM
  (
      SELECT
          email,
          `name`
      FROM
          multiple_tables_and_unions
  ) AS emails
  
  GROUP BY email
  </code></pre>
  <p>I have removed the code from the temporary table as it contains many tables but all select the <code>email</code> and <code>name</code> column. Essentially I need a function like <code>GROUP_COALESCE</code> but unfortunately it does not exist. What are my options?</p>