4

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.

Lukas Eder
  • 211,314
  • 129
  • 689
  • 1,509
RadRuss
  • 484
  • 2
  • 6
  • 16

2 Answers2

6

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;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

The easiest way to do this in Snowflake is to use Sequences. Pretty straight forward but they can be used to generate values for a primary key or any column that requires a unique value. How to use:

https://docs.snowflake.net/manuals/user-guide/querying-sequences.html

slybitz
  • 689
  • 1
  • 7
  • 18