How to find the max column value from more than one tables

I was needed to find out the maximum value for a column from 16 tables. The column was ROW_LAST_MODIFIED_THIS and I was needed to compare it with a source table field value, which contains the row_last_modified value at source side. The idea behind was to compare both the fields and get only records which are new or updated later than the max modification date. Here is how I figured it out:



SELECT * FROM table WHERE
 table.ROW_LAST_MODIFIED > NVL((select max(a) from(
(SELECT MAX(row_last_modified_this) as a
FROM table1) union all
(SELECT MAX(row_last_modified_this) as a
FROM table2) union all
(SELECT MAX(row_last_modified_this) as a
FROM table3) union all
(SELECT MAX(row_last_modified_this) as a
FROM table4) union all
(SELECT MAX(row_last_modified_this) as a
FROM table5) union all
(SELECT MAX(row_last_modified_this) as a
FROM table6) union all
(SELECT MAX(row_last_modified_this) as a
FROM table7) union all
(SELECT MAX(row_last_modified_this) as a
FROM table7) union all
(SELECT MAX(row_last_modified_this) as a
FROM table8) union all
(SELECT MAX(row_last_modified_this) as a
FROM table9) union all
(SELECT MAX(row_last_modified_this) as a
FROM table10) union all
(SELECT MAX(row_last_modified_this) as a
FROM table11) union all
(SELECT MAX(row_last_modified_this) as a
FROM table12) union all
(SELECT MAX(row_last_modified_this) as a
FROM table13) union all
(SELECT MAX(row_last_modified_this) as a
FROM table14)))
                                ,SYSDATE-36500)

You can use it with any number of tables. The field you are comparing should contain the similar value, like in this example it is date. Hope it will help :)