PostgreSQL: find minimum value across multiple columns but return column name-CodePudding

For each row in a table, I want to find the minimum value across a couple of numeric columns, then take the name of that column (which holds the desired value) and populate a new column with the name (or custom string).

A few rules first in my specific scenario: the value to be found across the columns must also be > 0. Also, if no value in the column is > 0, then a custom string should be placed (ie. 'none').

For example, take this table below with columns alpha to delta storing the values:

  id  | alpha  | bravo  | charlie | delta  
------ -------- -------- --------- --------
    1 |   5    |   2.3  |  -1     |  -5    
    2 |   9    |   8    |   3     |   1    
    3 |  -1    |  -4    |  -7     |  -9
    4 |   6.1  |   4    |   3.9   |   0

for each row, I want to find out which column holds the lowest positive value. My expected output is something like this:

  id  | alpha  | bravo  | charlie | delta  | lowest_postive
------ -------- -------- --------- -------- ---------------
    1 |   5    |   2.3  |  -1     |  -5    |  'col: bravo'
    2 |   9    |   8    |   3     |   1    |  'col: delta'
    3 |  -1    |  -4    |  -7     |  -9    |  'col: none'
    4 |   6.1  |   4    |   3.9   |   0    |  'col: charlie'

Should I use a CASE ... WHEN ... THEN ...? Should I be converting the row into an array first, then assinging each position in the array?

CodePudding user response：

You can do:

select *,
  case when mp = alpha then 'col: alpha'
       when mp = bravo then 'col: bravo'
       when mp = charlie then 'col: charlie'
       when mp = delta then 'col: delta'
  end as lower_positive
from (
  select *,
    least(
      case when alpha > 0 then alpha end,
      case when bravo > 0 then bravo end,
      case when charlie > 0 then charlie end,
      case when delta > 0 then delta end
    ) as mp
  from t
) x

However, this solution doesn't account for multiple minimums; the first one (from left ro right) wins.