DB2 学习笔记 - AIView.com

In some special case we could use ORDER BY clause instead of using MAX and MIN function. it makes the SQL more simple.

See following sample, the emphasis line is the row wanted.

select * from zytst.t@

part_num    group    plan_date
---------------------------------------
ADB               1            2006-02-12
ABB               1            2006-02-13
ACB               1            2006-02-13
AGB               2            2006-02-16

Following 2 SQLs get the same result in this case.

select part_num from zytst.t where group = 1
order by plan_date desc, part_num desc
fetch first 1 row only@ 

part_num    group    plan_date
---------------------------------------
ACB               1            2006-02-13

select max(part_num) 
from (select t1.part_num from zytst.t t1
        where t1.group = 1    
        and t1.plan_date =
            （select max(plan_date) from zytst.t t2
             where t2.group = t1.group) ) as t3
@

part_num    group    plan_date
---------------------------------------
ACB               1            2006-02-13