开发者

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';
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜