Least Function - How to Excluding Null and Zero values

Question

Hi All,

I am using, in a SQL Server Query, the Least(Calc1,Calc2, Cacl3) to return the smallest calculation.

Sometimes one of the calculations is zero or null.

From example:

Calc1 = 0 (or Null), Calc2 = 1, Calc3 = 2

How can I find the least of Calc2 and Calc3?

Least(Calc1,Calc2,Calc3)=1 (not zero) (in this example).

Many Thanks

Kody_Devl

Answer 1

Try least(nullif(Calc1, 0), nullif(Calc2, 0), nullif(Calc3, 0)).

Answer 2

Hi @Ross Crill

Refer to this document: Logical functions - LEAST (Transact-SQL)

If one or more arguments aren't NULL, then NULL arguments are ignored during comparison. If all arguments are NULL, then LEAST returns NULL.

Please have a check on Viorel's answer using NULLIF function. If the datatype of Calc1 is VARCHAR, then modify to nullif(Calc1, '0').

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.