MySQL get first non null value after group by

Question

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.

All of the UNION'd tables have an email column which will eventually be unique. The resulting records look like this:

1   [email protected]   Ozzy
2   [email protected]   Tony
3   [email protected]  Steve
4   [email protected]   
13  [email protected]  Tony
14  [email protected]   Ozzy
15  [email protected]  Dave
16  [email protected]   Tim

As you can see, some emails appear more then once with different names or non existent names. When I add a GROUP BY email clause at the end, the results look like this:

1   [email protected]   Ozzy
2   [email protected]   Tony
3   [email protected]  Steve
4   [email protected]   
13  [email protected]  Tony

As you can see, email 4 does not have a name because it chose the first entry with NULL for a name. Then I tried to use GROUP_CONCAT which made the results look like this:

1   [email protected]   Ozzy
14  [email protected]   Ozzy,Tony
15  [email protected]  Dave,Steve
16  [email protected]   Tim
13  [email protected]  Tony

As you can see, now everyone has a name but some rows have more then one name concatinated. What I want to do is GROUP BY email and choose the first NOT NULL entry of each column for each row to theoretically look like so:

1   [email protected]   Ozzy
2   [email protected]   Tony
3   [email protected]  Steve
4   [email protected]   Tim
13  [email protected]  Tony

I have tried using COALESCE but it doesnt work as intended. My current query looks like so:

SELECT
    id,
    email,
    `name`
FROM
(
    SELECT
        email,
        `name`
    FROM
        multiple_tables_and_unions
) AS emails

GROUP BY email

I have removed the code from the temporary table as it contains many tables but all select the email and name column. Essentially I need a function like GROUP_COALESCE but unfortunately it does not exist. What are my options?

Thanks

Answer 1

Try using MAX, like this:

SELECT
    email,
    MAX(`name`)
FROM
(
    SELECT
        email,
        `name`
    FROM
        multiple_tables_and_unions
) AS emails

GROUP BY email