How to assign UniqueID on a table basted on multiple columns

user_19027982 0
Viewed 32

I am wondering how to create a SQL Query to calculate if the row is unique based on certain columns(in this case InventoryID, CheckoutID, postal code, and UserID) to determine if its the first one that is unique then give it a value 1 if it has occurred again give it a 0.

Primary Key StoreID InventoryID CheckoutID Postal Code UserID
1 101 201 301 111 A1
2 101 201 301 111 A1
3 102 202 302 112 A2
4 103 203 303 113 A3
5 104 204 304 114 A4
6 104 205 305 114 A4
7 104 205 305 114 A4

I am looking to add a column or out how to get it to look like this:

Primary Key StoreID InventoryID CheckoutID Postal Code UserID unique
1 101 201 301 111 A1 1
2 101 201 301 111 A1 0
3 102 202 302 112 A2 1
4 103 203 303 113 A3 1
5 104 204 304 114 A4 1
6 104 205 305 114 A4 1
7 104 205 305 114 A4 0
snowflake-cloud-data-platform sql database
1 Answers

Assuming you are ordering by primary key, you can try this:

SELECT
    "Primary Key", StoreID, InventoryID, CheckoutID, "Postal Code", UserID,
    case WHEN row_num = 1 THEN 1 ELSE 0 END AS "unique"
FROM (
    SELECT
        *, ROW_NUMBER() OVER (PARTITION BY InventoryID, CheckoutID, "Postal Code", UserID ORDER BY "Primary Key") AS row_num
    FROM my_table
) foo;
user_146927690
Related