How to select the maximum value from several tables in one column

Question

I want the latest change date to be changed. Here is an example of a simple SELECT:

SELECT t01.name, t01.last_upd date1, t02.last_upd date2, t03.last_upd date3, 'maxof123' maxdate FROM s_org_ext t01, s_org_ext_x t02, s_addr_org t03 WHERE t02.par_row_id(+)= t01.row_id and t03.row_id(+)= t01.pr_addr_id and t01.int_org_flg = 'n'; 

How can I get the maxdate of a column to display a maximum of three dates?

Note: no UNION or sub / nested SELECT statements;)

Answer 1

Greatest (t01.last_upd, t02.last_upd date2, t03.last_upd) as maxdate

Answer 2

use the CASE statement in the SELECT clause to do something like:

 CASE WHEN date1 > date2 AND date1 > date3 THEN date1 WHEN date2 > date3 THEN date2 ELSE date3 END AS maxdate 

It will break out of logic as soon as the first condition is met.

Answer 3

Use the GREATEST function, also using explicit joins, not implicit joins.

 SELECT t01.name, t01.last_upd date1, t02.last_upd date2, t03.last_upd date3, GREATEST(t01.last_upd, t02.last_upd, t03.last_upd) AS maxdate FROM s_org_ext t01 LEFT OUTER JOIN s_org_ext_x t02 ON t01.row_id = t02.par_row_i LEFT OUTER JOIN s_addr_org t03 ON t01.pr_addr_id = t03.row_id WHERE t01.int_org_flg = 'n';