TIL how to set a lower threshold on a column in SQL

For the last couple of months I have come across multiple instances where I wanted to write an SQL query to apply a lower threshold to 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.

with example_table as (
SELECT * 
FROM (
    VALUES 
        (1, -100.423), 
        (2, 5.0), 
        (3, -0.001), 
        (4, 100.0), 
        (5, 12.0)
) AS t (row_nr, data_column)
)

select GREATEST(DATA_COLUMN, 0.) as thresholded 
from 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).