For the last couple of months I came across multiple instances where I wanted to write an SQL query to lower threshold all the values in a particular column in one of our Snowflake tables.

I used quite clunky workarounds to do that. It turns out you can use the SQL functions GREATEST or LEAST for it.

Example

I am creating a temporary table in the first CTE with a column DATA_COLUMN that has positive and negative numbers.

 1with example_table as (
 2SELECT * 
 3FROM (
 4    VALUES 
 5        (1, -100.423), 
 6        (2, 5.0), 
 7        (3, -0.001), 
 8        (4, 100.0), 
 9        (5, 12.0)
10) AS t (row_nr, data_column)
11)
12
13select GREATEST(DATA_COLUMN, 0.) as thresholded 
14from example_table

Output:

0.000
5.000
0.000
100.000
12.000

The function’s primary purpose is to take the greatest value among a number of referenced columns, e.g. GREATEST(column1, column2, column3).