CODEWITHSUNDEEP
sqlpostgresql
levi
levi

Reputation: 22697

Selecting max value between values PostgreSQL

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 ?

Upvotes: 1

Views: 3052

Answers (2)

klin
klin

Reputation: 121554

Use greatest():

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.

Upvotes: 6

Gordon Linoff
Gordon Linoff

Reputation: 1269603

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;

Upvotes: 1

Related Questions