Get row with largest negative number, or row with smallest number if there are no negative numbers

Question

I'm trying to write a Snowflake SQL statement that does the following:

• If there are negative numbers in column_A, return the row with the biggest negative number in column_A
• If there are no negative numbers in column_A, return the row with the smallest number in column_A

For example, if my table is:

column_A	column_B
-20	1
-5	2
1	3
15	4

The result should be: -5, 2

If my table is:

column_A	column_B
1	3
15	4
20	5

The result should be: 1, 3

To create an example table:

with example_table as (
    select
            $1::NUMBER as column_A
          , $2::NUMBER as column_B
    from
        (values
                 (-20, 1) 
               , (-5, 2)
               , (1, 3) 
               , (15, 4) 
        )
)

select * from example_table

Answer 1

Something like:

order by
    case when column_a < 0 then 1 else 2 end,
    abs(column_a)
offset 0 rows
fetch first 1 row only

Basically you order by on two faux columns:

• First one will contain 1 for all negative values and 2 otherwise so this puts all negative values first, if any
• Second one will contain the absolute value (e.g. -5 becomes 5 whereas 5 remains 5)