Consider the following, in PostgreSQL:

drop table test ;
create table test (result1 text, red smallint, green smallint, blue smallint, results2 text) ;
insert into test values ('red',1,2,3) ;

I would like results2 to contain the value blue, indicating that the maximum value for the integer columns occurs in column blue. This can be achieved by running an UPDATE or a function at a later time.

How do I insert the column name as a value? As an example, the row above would be updated such that it contains:

'red', 1, 2, 3, 'blue'

There will not be NULL values for the integer columns and one of the integers will always be the largest.

(See also here.)

  • That could still be done in a view. Which DBMS are you using? – a_horse_with_no_name Jun 1 '16 at 10:01
  • 1
    What should the column contain if two or more columns have the same value? You would need an update to change the value later, not an insert. But what is the real problem you are trying to solve here? This sounds pretty weird and maybe the underlying problem should be solved in a different way. – a_horse_with_no_name Jun 1 '16 at 10:04

You could create a view that looks like something like this:

create view test_view
select a,b,c,d,
          when a = greatest(a,b,c,d) then 'a' 
          when b = greatest(a,b,c,d) then 'b' 
          when c = greatest(a,b,c,d) then 'c' 
          when d = greatest(a,b,c,d) then 'd' 
from test;

Note that the above will not work correctly if any of the columns contains a null value because greatest() will return null then. If you need to handle that, you need to use e.g.:

when a = greatest(coalesce(a,0),coalesce(b,0),coalesce(c,0),coalesce(d,0)) then 'a' 

When two columns have the same highest value this would display the "first" one. So for (2,2,4,4) it would return c

  • [This answer correctly addresses an earlier version of the question. Note that a,b,b,c should be a,b,c,d.] – SabreWolfy Jun 1 '16 at 10:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.