How to find the value closest to zero using SQL Se

问题:

Let's say I have a table that could have various numbers in it, for example it could look like the following:

ExampleA: MyTable -10, -3, 5, 10,

ExampleB: MyTable -10, -5, 3, 10,

So if I queried the table in ExampleA I'd want it to return "-3" (The value closet to 0)

Likewise if I queried the table in ExampleB I'd want it to return "3" (The value closest to 0)

I always want to find the value that is closest to zero regardless of the numbers in the table, how can I do this?

Also, how could I choose which value for ties (like in the case where the closest value may be -3 and 3)?

回答1:

Use a combination of min() and abs():

select num
from mytable
where abs(num) = (select min(abs(num)) from mytable)

To break ties, apply min() or max() to num to get the negative or positive side, eg

To get the negative of a tie:

select min(num) num 
from mytable
where abs(num) = (select min(abs(num)) from mytable)

To get the positive of a tie:

select max(num) num
from mytable
where abs(num) = (select min(abs(num)) from mytable)

回答2:

Try

Select top 1 with ties num
From tbl
Group by num
Order by abs(num) asc

Demo