Remove duplicate records with special rules - Is it possible with a single query?
WITH cte AS ( SELECT *,
                     ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
              FROM test )
SELECT id, loc1, loc2, zip_code
FROM cte
WHERE rn = 1
ORDER BY id;



If I want to use this for deleting the duplicates, all I need to do is change WHERE rn = 2. Is that correct? – advncd
@advncd No, MySQL does not support updatable CTE. You must use this query as a subquery which selects rows which must be stored while deleting from another copy of source table.

DELETE t1.*
FROM test t1
JOIN (WITH cte AS ( SELECT *,
                           ROW_NUMBER() OVER (PARTITION BY id ORDER BY zip_code IS NULL, loc1 IS NULL) rn
                    FROM test )
      SELECT id, loc1, loc2, zip_code
      FROM cte
      WHERE rn &gt; 1) t2 ON t1.id = t2.id
                      AND t1.loc1 &lt;=&gt; t2.loc1
                      AND t1.loc2 &lt;=&gt; t2.loc2
                      AND t1.zip_code &lt;=&gt; t2.zip_code;

fiddle