Oracle MIN as analytic function - odd behavior with ORDER BY?

If you add an ORDER BY to the MIN analytic function, you turn it into a "min so far" function rather than an overall minimum. For the final row for whatever you're partitioning by, the results will be the same. But the prior rows may have a different "min so far" than the overall minimum.

Using the EMP table as an example, you can see that the minimum salary so far for the department eventually converges on the overall minimum for the department. And you can see that the "min so far" value for any given department decreases as lower values are encountered.

SQL> edWrote file afiedt.buf  1  select ename,  2         deptno,  3         sal,  4         min(sal) over (partition by deptno order by ename) min_so_far,  5         min(sal) over (partition by deptno) min_overall  6    from emp  7*  order by deptno, enameSQL> /ENAME          DEPTNO        SAL MIN_SO_FAR MIN_OVERALL---------- ---------- ---------- ---------- -----------CLARK              10       2450       2450        1300KING               10       5000       2450        1300MILLER             10       1300       1300        1300ADAMS              20       1110       1110         800FORD               20       3000       1110         800JONES              20       2975       1110         800SCOTT              20       3000       1110         800smith              20        800        800         800ALLEN              30       1600       1600         950BLAKE              30       2850       1600         950MARTIN             30       1250       1250         950SM0                30        950        950         950TURNER             30       1500        950         950WARD               30       1250        950         950BARPAV16 rows selected.

Of course, it would make more sense to use this form of the analytic function when you're trying to do something like compute a personal best that you can use as a comparison in future periods. If you're tracking an individual's decreasing golf scores, mile times, or weight, displaying personal bests can be a form of motivation.

SQL> edWrote file afiedt.buf  1  with golf_scores as  2  (  select 1 golfer_id, 80 score, sysdate dt from dual union all  3     select 1, 82, sysdate+1 dt from dual union all  4     select 1, 72, sysdate+2 dt from dual union all  5     select 1, 75, sysdate+3 dt from dual union all  6     select 1, 71, sysdate+4 dt from dual union all  7     select 2, 74, sysdate from dual )  8  select golfer_id,  9         score, 10         dt, 11         (case when score=personal_best 12               then 'New personal best' 13               else null 14           end) msg 15    from ( 16  select golfer_id, 17         score, 18         dt, 19         min(score) over (partition by golfer_id 20                              order by dt) personal_best 21    from golf_scores 22*        )SQL> / GOLFER_ID      SCORE DT        MSG---------- ---------- --------- -----------------         1         80 12-SEP-11 New personal best         1         82 13-SEP-11         1         72 14-SEP-11 New personal best         1         75 15-SEP-11         1         71 16-SEP-11 New personal best         2         74 12-SEP-11 New personal best6 rows selected.