Select the max value on a multiple table join

Question

I am trying to get the maximum date out of multiple tables when those table have a particular geometry. My tables more or less look like that (of course they're all different but I shortened them in order to make it clearer ):

A table type :

Id, Info, Geometry, Date

And finally I have an other table that looks like that (shortened again) :

B table:

Id, Geometry

Now, what I want to do is to join all my A type tables on Geometry where they intersect with the B table Geometry, and to get the A table that has the most recent date.

I currently have the following request which is working:

UPDATE last_updateT SET date_last_update= S.dateMax 
FROM
    (SELECT B.gid, MAX(A.last_date) AS dateMax
    FROM B
    JOIN A ON ST_Intersects(B.geometry, A.geometry)
    GROUP BY B.gid) S
WHERE T.id = S.gid;

Now I'd like to be able to do that kind of join on multiple table that looks like table A. I've heard of the function GREATEST but I am not sure about how to use it. Also, I use Postgresql if that makes any differences.

Answer 1

In broad strokes, MAX is an aggregate function, so you use MAX to get the highest value from the same column over a number of different rows.

GREATEST is a scalar function, you use GREATEST to get the highest value from different columns in the same row.

eg:

SELECT GREATEST(col1,col2,col3)

Greatest: https://www.postgresql.org/docs/9.5/static/functions-conditional.html

Max: https://www.postgresql.org/docs/9.5/static/functions-aggregate.html