CODEWITHSUNDEEP
snowflake-cloud-data-platform
Jenifer
Jenifer

Reputation: 347

Need guidance in inserting single record into target table

I have multiple records in source table. I have to insert the record from source table to target table. For this, I need to sequence all the records based on ITEM_NO and take the last NOT NULL values for ITEM_SEARCH_CATEGORY column.

If there are totally 5 records coming from source and if fifth record for ITEM_SEARCH_CATEGORY is NULL, then I need to take the fourth record and insert into the target table.

Similarly if the last 2 records for the ITEM_SEARCH_CATEGORY column is NULL, then I need to take the 3rd record and insert that it into target table.

How to write the correct query for this logic?

ID          DATE          ITEM_NO        ITEM_SEARCH_CATEGORY
12538648    2022-03-03    201            itemsearch:input:suggestion
12538648    2022-03-03    175            NULL
12538648    2022-03-03    160            itemsearch:standard
12538648    2022-03-03    213            itemsearch:input:suggestion
12538648    2022-03-03    198            NULL

I have tried the below but I don't think this seems to be right.

SELECT distinct
    ID,
    DATE,
    ITEM_NO,
    ITEM_SEARCH_CATEGORY
FROM TABLE_1 A JOIN TABLE_2 B
ON B.IID=A.IID
WHERE DATE='2021-11-06'
    AND ID=12538648
QUALIFY row_number() over (partition by ID order by DATE desc) = 1

Upvotes: 0

Views: 95

Answers (1)

Simeon Pilgrim
Simeon Pilgrim

Reputation: 25928

There are WINDOW functions like LAST_VALUE, LAG, MAX, MIN that pick for this column one value base on logic of the function. And in Snowflake they have a nice IGNORE NULLS clause thus:

GROUPS ORDERS VALUES
a 1 100
a 2 200
a 3 null
b 1 101
b 2 201
b 3 301

for we can PARTITION BY groups and then ORDER BY orders and get the LAST_VALUE

aka

LAST_VALUE(values) IGNORE NULLS OVER (PARITION BY groups ORDER BY orders) as values

and insert that into a table, but they are "per row" selecting the LAST_VALUE, thus if we select everything from our TABLE, and the LAST_VALUE as define by the SQL, we would have

GROUPS ORDERS VALUES LAST_VALUE
a 1 100 200
a 2 200 200
a 3 null 200
b 1 101 301
b 2 201 301
b 3 301 301

So WINDOW FUNCTIONs select a single value of a WINDOW of future/prior ROWS for this column.

Then we would have the problem of selecting/filter those row down to JUST one row. So you could use DISTINCT which you have done in many places. OR you could use a WHERE clause or QUALIFY. But you have to think what other values of the row are you wanting.

So for the example data, do you want to insert into your results, the values

GROUPS ORDERS VALUES
a 3 200
b 3 301

which is the last row, with the LAST_VALUE of VALUES, or do you want, or it could also the the MAX(ORDERS), MAX(VALUES)

or do you want the related details of the row with the largest VALUES that is not null, aka

GROUPS ORDERS VALUES
a 2 200
b 3 301

At which point you will need some form or ROW_NUMBER/RANK and then WHERE the best (=1) or use a single line QUALIFY which is the same, but requires less layers, but is more intention dense.

Upvotes: 1

Related Questions