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.
It seems you are looking for UNION ALL, so you can treat the data from different tables as if it were data from only one table:
SELECT
b.gid,
MAX(x.last_date) AS dateMax
FROM b
JOIN
(
SELECT geometry, last_date FROM a
UNION ALL
SELECT geometry, last_date FROM aa
UNION ALL
SELECT geometry, last_date FROM aaa
) x ON ST_Intersects(b.geometry, x.geometry)
GROUP BY b.gid;
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