Get the minimum non zero value across multiple col

问题:

Let's say I have the following table:

CREATE TABLE numbers
(
  key integer NOT NULL DEFAULT 0,
  number1 integer NOT NULL DEFAULT 0,
  number2 integer NOT NULL DEFAULT 0,
  number3 integer NOT NULL DEFAULT 0,
  number4 integer NOT NULL DEFAULT 0,

  CONSTRAINT pk PRIMARY KEY (key),
  CONSTRAINT nonzero CHECK (key <> 0)
)

What I want to retrieve is the minimum value from a given key of all 4 numbers, but ignoring those that are zero.

I started with something like this when I figured that I'll have problem with the zeros:

SELECT LEAST(number1, number2, number3, number4) FROM numbers WHERE key = 1

For instance, if I have the tuple (1, 5, 0, 3, 2) I want to return 2, or for (2, 3, 0, 0, 0) I want to return 3 and so on.

Can this be done in a single query (or maybe nested), I don't really want to have to write a procedure to do this.

回答1:

Try NULLIF function:

SELECT LEAST(
        nullif( number1, 0 ), 
        nullif( number2, 0 ), 
        nullif( number3, 0 ), 
        nullif( number4, 0 )) 
FROM numbers

Demo --> http://www.sqlfiddle.com/#!12/641fb3/1

回答2:

LEAST will ignore NULL values, so the following should work:

select least(n1, n2, n3, n4)
from (
   select case when number1 <= 0 then null else number1 end as n1,
          case when number2 <= 0 then null else number2 end as n2,
          ...
    from numbers
) t

This way you can also deal with negative numbers. If you are sure there will never be negative values, kordirko's answer is probably a bit easier.