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

Question

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?

The Impaler · Accepted Answer · 2022-01-31 00:02:06Z

1

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.

answered Jan 31, 2022 at 0:02

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Theo F Over a year ago

This works well. It just needs an else 'col: none' after line 5 as a catch.

Theo F Over a year ago

This works great when dealing with a handful of columns. But say there were 100 columns and you didn't want to write out case .. when ... then 100 times... how'd you go about that? This time, assume that custom strings aren't required in the new output column.

The Impaler Over a year ago

@TheoF If the table has 100 separate columns, this solution or a similar one is the way to go. Now, if you had a single column of type array, then that could be processed with a different query.

Theo F Over a year ago

you say the first one from left to right wins, but how could you make it so the furthest right wins?

The Impaler Over a year ago

@TheoF To make it so the first one (from right to left) wins, change line 2-5 and place the WHEN ... clauses in the opposite order.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related