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 |
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.
I would recommend unpivoting the columns to rows with a lateral join, then using distinct on to get the top column/value pair per id:
select distinct on (t.id) t.id, x.*
from mytable t
cross join lateral ( values ('pos', t.pos), ('neu', t.neu), ('neg', t.neg) ) x(col, val)
order by t.id, x.val desc, x.col
-- ^ tie-breaker
On a large dataset, it might be more efficient to move the row-limitng logic within the lateral join:
select t.id, x.*
from mytable t
cross join lateral (
select x.*
from ( values ('pos', t.pos), ('neu', t.neu), ('neg', t.neg) ) x(col, val)
order by x.val desc, x.col
limit 1
) x
| id | col | val |
|---|---|---|
| x | neg | 3 |
| y | pos | 4 |
| z | neu | 5 |
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;
