CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platformmin
jeena p
jeena p

Reputation: 51

In Snowflake how to find smallest value for each row from 'N' number of columns without including NULL values

I have table with columns like Col1, Col2, Col3... ColN

The table value is something like this

ID Col1 Col2 Col3 Col4 Expected Output
1 10 NULL 500 5 5
2 15 1 400 NULL 1
3 20 2 NULL 10 2

I tried

SMALLEST(VAL1,VAL2, VAL3... VALN)

But it returns NULL value. I want to ignore NULL Values and compare non-null values.

Please help on this. Thanks

Upvotes: 2

Views: 1677

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 175924

Using ARRAY_CONSTRUCT_COMPACT to remove NULL values:

SELECT t.ID, t.Col1, t.Col2, t.Col3, t.Col4, MIN(value) AS least
FROM tab AS t
, TABLE(FLATTEN(INPUT => ARRAY_CONSTRUCT_COMPACT(t.Col1, t.Col2, t.Col3, t.Col4)))
GROUP BY t.ID, t.Col1, t.Col2, t.Col3, t.Col4
ORDER BY t.ID;

Sample input:

CREATE OR REPLACE TABLE Tab(ID INT, COL1 INT, Col2 INT, Col3 INT, Col4 INT)
AS
SELECT 1,10,NULL,500,5 UNION
SELECT 2,15,1,400,NULL UNION
SELECT 3,20,2,NULL,10;

Output:

enter image description here

EDIT: Using ARRAY_MIN:

WITH cte(ID , COL1 , Col2 , Col3 , Col4 ) AS (
  SELECT 1,10,NULL,500,5 UNION
  SELECT 2,15,1,400,NULL UNION
  SELECT 3,20,2,NULL,10
)
SELECT *, ARRAY_MIN([COL1, COL2, COL3, COL4])
FROM cte;

enter image description here

Upvotes: 4

Marcel
Marcel

Reputation: 2622

Check out LEAST: https://docs.snowflake.com/en/sql-reference/functions/least.html

Unfortunately, by default LEAST is considering NULL as well. To avoid this, you can apply a COALESCE for each parameter containing all other columns you would like to check. This doesn’t look super pretty but should work:

LEAST(COALESCE(col1,col2,col3), COALESCE(col2,col1,col3), COALESCE(col3, col1, col2))

Only if all columns are null the output is null.

Upvotes: 0

Related Questions