How do I select the max value from multiple tables in one column
I would like to get开发者_如何学运维 the last date of records modified. Here is a sample 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 column maxdate to display the max of the three dates?
Note: no UNION or sub/nested SELECT statements ;)
Greatest (t01.last_upd, t02.last_upd date2, t03.last_upd ) as maxdate
use a CASE statement in your 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 the logic as soon as the first condition is met.
Use the GREATEST
function, also used explicit joins rather than 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';
精彩评论