CODEWITHSUNDEEP
sqlduplicatessnowflake-cloud-data-platform
Micah
Micah

Reputation: 23

In Snowflake, I want to count duplicates in a table based on all the columns in the table without typing out every column name

I have a table with 60 columns in it. I would like to identify how many duplicates there are in the table based on all the columns being identical.

I don't want to have to type out every field name in the SELECT or GROUP BY clauses. Is there a way to do that?

Upvotes: -1

Views: 902

Answers (1)

Jim Demitriou
Jim Demitriou

Reputation: 603

You can use an approach like this for each table:

    SELECT
    MD5(OBJECT_CONSTRUCT(SRC.*)::VARCHAR) DUP_MD5, SUM(1) AS TOTAL_COUNT
    FROM <table> SRC
    GROUP BY 1
    HAVING SUM(1) > 1;

Upvotes: 1

Related Questions