Question 171951 in PostgreSQL Question posted in
The official documentation can be found here.

What is the column name of the greatest item in Postgresql SQL Select?

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
Tags:

2

Answers


  1. 0

    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;
    Login or Signup to reply.
  2. 0

    There is an elegant solution that is independent of the number of columns:

    select distinct on (id) 
    id, 
    value as greatest, 
    key as column
from my_table t
cross join jsonb_each(to_jsonb(t)- 'id')
order by id, value desc;

    Test it in db<>fiddle.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search