SQL Select greatest and get the column name

Question

I have such table (simplified)

id	pos	neu	neg
x	1	2	3
y	4	2	1
z	1	5	2

I use PostgreSQL and my goal is to get greatest value for each row, so i use next query:

SELECT id, GREATEST(pos, neg, neu) FROM my_table;

In response i have smth like:

id	greatest
x	3
y	4
z	5

But is there a way i can know which column these values are belong to?

Expected smth like:

id	greatest	column
x	3	neg
y	4	pos
z	5	neu

Answer 1

You could use a case expression such as this:

select id, greatest(pos, neg, neu),
  case greatest(pos, neg, neu) 
    when pos then 'pos' 
    when neg then 'neg' 
    when neu then 'neu' 
  end as ColumnName
from t;