Adding row IDs to table in Snowflake SQL

Question

I need to add a column with unique integers/hashes to a table to serve as the row id.

I created a table DUPLICATES with this query:

CREATE TABLE duplicates AS
SELECT
    "a", "b", COUNT(*) as _count
FROM
    "table"
GROUP BY
    "a", "b"
HAVING 
    _count > 1
ORDER BY 
 _count desc

Now I need to add a column that would contain unique ID for each row. I tried ALTER TABLE DUPLICATES ADD 'id' int identity(1,1), however this returns: SQL compilation error: Cannot add column 'id' with non-constant default to non-empty table 'DUPLICATES'.

I am kind of short of ideas now as the discussions on Snowflake SQL are not widespread yet and the help page of Snowflake did not help me.

Answer 1

One simple method is to use row_number():

CREATE TABLE duplicates AS
    SELECT ROW_NUMBER() OVER (ORDER BY COUNT(*) DESC) as id,
           "a", "b", COUNT(*) as _count
    FROM "table"
    GROUP BY "a", "b"
    HAVING  _count > 1
    ORDER BY _count desc;