Menu
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 concatenated. 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 doesn't 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?
722
SQL COALESCE – a function that returns the first defined, i.e. non-NULL value from its argument list. Usually one or more COALESCE function arguments is the column of the table the query is addressed to. Often a subquery is also an argument for a function.
Below is the syntax to filter the rows without a null value in a specified column. Syntax: SELECT * FROM <table_name> WHERE <column_name> IS NOT NULL; Example: SELECT * FROM demo_orders WHERE ORDER_DATE IS NOT NULL; --Will output the rows consisting of non null order_date values.
Answer: No. We can't have a Primary Key column with a NULL value. The reason for the same is very simple, primary key purpose is to uniquely identify records. If two records of a single column have a NULL value, the column values are not considered equal.
Hi, In composite primary key columns you cannot pass null values. Each column defined as a primary key would be validated so that null values are not passed on to them. If you have given a Unique constraint then we have a chance of NULL values being accepted.
Try using MAX, like this:
SELECT email, MAX(`name`) FROM ( SELECT email, `name` FROM multiple_tables_and_unions ) AS emails GROUP BY email If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
