Add Unique Identifier column in SQL View-CodePudding

I'm using SQL in Databricks and I have a view of a table which looks like this:

COL_A   |    COL_B    | 
-----------------------
AA      |    AB       |   
AA      |    AC       |   
AA      |    AD       |    
AA      |    AE       |

I would like to create a column which increments by 1 starting from 100, such that I get data looking like this:

COL_A   |    COL_B    |    COL_C
---------------------------------
AA      |    AB       |    100
AA      |    AC       |    101
AA      |    AD       |    102
AA      |    AE       |    103

I've tried using code with the IDENTITY(100,1) and AUTO_INCREMENT functions, but I don't think they work with SQL view and/or databricks. If anyone has any ideas I'd greatly appreciate it, thank you!

CodePudding user response：

I think you need to learn about SEQUENCE, something like this:

CREATE SEQUENCE id_seq
    INCREMENT BY 1
    START WITH 100
    MINVALUE 10
    MAXVALUE 1000
    CYCLE
    CACHE 2;

CodePudding user response：

Ideally you can just do this to the table in question:

    ALTER TABLE ADD Col_C (UNIQUEIDENTIFIER or INT IDENTITY(1, 1) if supported) NOT NULL

If for some reason you can't do that and you can only uniquely identify something in the view query, I would recommend looking at window functions. These are supported in a variety of SQL flavors including SQL Server, SQLite, MySQL, and hopefully data bricks, though I'm unfamiliar with this technology.