CODEWITHSUNDEEP
sqlsql-servernumberszero
user2989991
user2989991

Reputation: 43

How to find the value closest to zero using SQL Server

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)?

Upvotes: 4

Views: 4623

Answers (2)

T I
T I

Reputation: 9933

Try

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

Demo

Upvotes: 3

Bohemian
Bohemian

Reputation: 424993

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)

Upvotes: 5

Related Questions