Selecting max value between values PostgreSQL

Question

Let's say I want to select max attribute between two columns on a query. it isn't the whole table max.

Something like that:

SELECT MAX(t1.c1, t2.c2)
FROM Table1 as t1, Table2 as t2
WHERE t1.id = t2.t1_id
AND ...... here goes more conditions.

I want to each row has mapped value representing the max between t1.c1 and t2.c2

Is that possible ?

Sample data and expected results would be helpful here... Also look at using a join instead. — sgeddes
– sgeddes, Commented Sep 8, 2016 at 1:28

klin · Accepted Answer · 2016-09-08 01:36:53Z

6

SELECT greatest(t1.c1, t2.c2)
FROM Table1 as t1
JOIN Table2 as t2 ON t1.id = t2.t1_id
-- WHERE ...... here goes more conditions

Note, I have changed your notation of join to more convenient.

answered Sep 8, 2016 at 1:36

klin

123k15 gold badges240 silver badges262 bronze badges

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

Comments

Gordon Linoff · Accepted Answer · 2016-09-08 01:29:42Z

1

Use union all:

select max(c1)
from (select c1 from t1 union all select c2 from t2) t;

Or -- probably more efficient -- use greatest():

select greatest(c1, c2)
from (select max(c1) as c1 from t1) t1 cross join
     (select max(c2) as c2 from t2) t2;

answered Sep 8, 2016 at 1:29

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

where should I add more conditions ? like my answer. I updated it.

You would add them in the subqueries.