In my Snowflake SQL query I have an output like below, all other fields contain the same data when grouped by Column A:

The query uses both SELECT DISTINCT and GROUP BY clauses to remove any duplication where it exists. I’m aware that these are not true ‘duplicates’ but I’d like to remove the rows i’ve tagged as 1 and 2 as they have NULLS, so my output should look like this:

I’ve already tried using ROW_NUMBER(), PARTITION BY, RANK() etc to eliminate the rows i don’t require, but as rows tagged 3 and 4 legitimately contain NULLS it also removes them. There are no other fields (for example datetime) that i could use to order by and remove based on them.

A slimmed down example of my current query is:

SELECT
COLUMN A,
COLUMN B,
COLUMN C,
COLUMN D,
COLUMN E,
COLUMN F
FROM
(
    WITH CTE1 AS
    (
        SELECT DISTINCT
        COLUMN A,
        COLUMN B,
        COLUMN C,
        COLUMN D,
        COLUMN E,
        COLUMN F
        FROM
        SOURCE_TABLE WHERE SOURCE = '1'
    ),
    CTE2 AS
    (
        SELECT DISTINCT
        COLUMN A,
        COLUMN B,
        COLUMN C,
        COLUMN D,
        COLUMN G,
        COLUMN H
        FROM
        SOURCE_TABLE WHERE SOURCE = '2'
    ),   
    CTE3 AS
    (
        SELECT DISTINCT
        COLUMN A,
        COLUMN B,
        COLUMN C,
        COLUMN D,
        COLUMN E,
        COLUMN F
        FROM
        SOURCE_TABLE WHERE SOURCE = '3'
    ),  
    BASE_CTE AS
    (
        SELECT DISTINCT
        COLUMN A,
        COLUMN B,
        COLUMN C,
        COLUMN D,
        MAX(COLUMN E) AS COLUMN E,
        LISTAGG(COLUMN F, ', ') AS COLUMN F,
        FROM
        (
            SELECT
            COLUMN A,
            COLUMN B,
            COLUMN C,
            COLUMN D,
            COLUMN E,
            COLUMN F
            FROM
            CTE1
            UNION
            (
                SELECT
                COLUMN A,
                COLUMN B,
                COLUMN C,
                COLUMN D,
                COLUMN E,
                COLUMN F
                FROM
                CTE2
            )
            UNION
            (
                SELECT
                COLUMN A,
                COLUMN B,
                COLUMN C,
                COLUMN D,
                COLUMN E,
                COLUMN F
                FROM
                CTE3
            )
        ) GROUP BY ALL
    )
    SELECT * FROM BASE_CTE
);

Hopefully I’ve missed something obvious, and as this is my first post please do let me know if i need to add more information or details.