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:

The result should be: -5, 2

If my table is:

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

CodePudding user response：

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)

CodePudding user response：

It could be achieved using SIGN and ABS:

SELECT * 
FROM example_table
ORDER BY SIGN(COLUMN_A), ABS(COLUMN_A) LIMIT 1;

Sign returns -1 for negative, 0 for zero and 1 for positive numbers. ABS returns absoute value sorted ascending. LIMIT 1 restrict resultset to single row.

Output:

CodePudding user response：

In SQL, I would write :

SELECT (
    IFF (
        (SELECT COUNT(*) FROM myTable WHERE column_A < 0) > 0,
        SELECT * FROM myTable WHERE column_A = MAX(column_A),
        SELECT * FROM myTable WHERE column_A = MIN(column_A)
    ) );

Here is the documentation for IFF : https://docs.snowflake.com/en/sql-reference/functions/iff.html