CODEWITHSUNDEEP
sqlazure-synapse
Victor Ng
Victor Ng

Reputation: 71

How do I find the closest number across columns?

I have this table:

col_1 | col_2 | col_3 | compare
------+-------+-------+--------
 1.1  | 2.1   | 3.1   | 2
------+-------+-------+--------
 10   | 9     | 1     | 15

I want to derive a new column choice indicating the column closest to the compare value:

col_1 | col_2 | col_3 | compare | choice
------+-------+-------+---------+-------
 1.1  | 2.1   | 3.1   | 2       | col_2
------+-------+-------+---------+-------
 10   | 9     | 1     | 15      | col_1

Choice refers to the column where cell value is closest to the compare value.

Upvotes: 2

Views: 43

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270713

I think the simplest method is apply:

select t.*, v.which as choice
from t cross apply
     (select top (1) v.*
      from (values ('col_1', col_1), ('col_2', col_2), ('col_3', col_3)
           ) v(which, val)
      order by abs(v.val - t.compare)
     ) v;

In the event of ties, this returns an arbitrary closest column.

You can also use case expressions, but that gets complicated. With no NULL values:

select t.*,
       (case when abs(compare - col_1) <= abs(compare - col_3) and
                  abs(compare - col_1) <= abs(compare - col_3)
             then 'col_1'
             when abs(compare - col_2) <= abs(compare - col_3)
             then 'col_2'
             else 'col_3'
         end) as choice
from t;

In the event of ties, this returns the first column.

Upvotes: 1

Related Questions