| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: getting the nearest number from a column
> FWIW, The behavior is as specified in the SQL Standard.
> The trick here lies in in the ORDER BY clause.
> The usage of ORDER BY in OLAP implies that order matters to the user and
> hence windowing semantics are used.
> If ORDER BY is ommitted then MIN (and others) return the same value for
> the whole partition.
>
> Cheers
> Serge
Yeahhhhhh you are completely right of course and only today when I got
similar problem I reread this thread again and fully understood what
you said. Probably I'm a bit slow (or rather carelessly read your
post) but as people always evaluate other people looking from their
own perspective I'll show to others what I understood :)
1) if one doesn't use any clause in analytic function (one partition,
the REAL max for salary)
SQL> select ename, deptno, sal, max(sal) over () mxsal
2 from emp;
ENAME DEPTNO SAL MXSAL ---------- ---------- ---------- ----------
SMITH 20 800 5000 ALLEN 30 1600 5000 WARD 30 1250 5000 JONES 20 2975 5000 MARTIN 30 1250 5000 BLAKE 30 2850 5000 CLARK 10 2450 5000 SCOTT 20 3000 5000 KING 10 5000 5000 TURNER 30 1500 5000 ADAMS 20 1100 5000 JAMES 30 950 5000 FORD 20 3000 5000 MILLER 10 1300 5000
2) if one wants now order by ename then local minimum (i.e. with
default windowing clause from unbounded preceding to current row) is
used:
1 select ename, deptno, sal, max(sal) over (ORDER BY ename) mxsal
2* from emp
SQL> /
ENAME DEPTNO SAL MXSAL
---------- ---------- ---------- ----------
ADAMS 20 1100 1100 ALLEN 30 1600 1600 BLAKE 30 2850 2850 CLARK 10 2450 2850 FORD 20 3000 3000 JAMES 30 950 3000 JONES 20 2975 3000 KING 10 5000 5000 MARTIN 30 1250 5000 MILLER 10 1300 5000 SCOTT 20 3000 5000 SMITH 20 800 5000 TURNER 30 1500 5000 WARD 30 1250 50003) OK we can revert back to the first case using windowing clause that forces max for all partition
ADAMS 20 1100 5000 ALLEN 30 1600 5000 BLAKE 30 2850 5000 CLARK 10 2450 5000 FORD 20 3000 5000 JAMES 30 950 5000 JONES 20 2975 5000 KING 10 5000 5000 MARTIN 30 1250 5000 MILLER 10 1300 5000 SCOTT 20 3000 5000 SMITH 20 800 5000 TURNER 30 1500 5000 WARD 30 1250 5000
4) OK now lets start adding partition clause - as a result we got
maximum for each partition:
1 select ename, deptno, sal, max(sal) over
2 (PARTITION BY deptno) mxsal
3* from emp
SQL> /
ENAME DEPTNO SAL MXSAL
---------- ---------- ---------- ----------
CLARK 10 2450 5000 KING 10 5000 5000 MILLER 10 1300 5000 SMITH 20 800 3000 ADAMS 20 1100 3000 FORD 20 3000 3000 SCOTT 20 3000 3000 JONES 20 2975 3000 ALLEN 30 1600 2850 BLAKE 30 2850 2850 MARTIN 30 1250 2850 JAMES 30 950 2850 TURNER 30 1500 2850 WARD 30 1250 28505) next variants should be quite obvious - if we now add order by we get local minimum again:
CLARK 10 2450 2450 KING 10 5000 5000 MILLER 10 1300 5000 ADAMS 20 1100 1100 FORD 20 3000 3000 JONES 20 2975 3000 SCOTT 20 3000 3000 SMITH 20 800 3000 ALLEN 30 1600 1600 BLAKE 30 2850 2850 JAMES 30 950 2850 MARTIN 30 1250 2850 TURNER 30 1500 2850 WARD 30 1250 28506) adding windowing clause to force to scan all the partition we got maxes for all partition again:
CLARK 10 2450 5000 KING 10 5000 5000 MILLER 10 1300 5000 ADAMS 20 1100 3000 FORD 20 3000 3000 JONES 20 2975 3000 SCOTT 20 3000 3000 SMITH 20 800 3000 ALLEN 30 1600 2850 BLAKE 30 2850 2850 JAMES 30 950 2850 MARTIN 30 1250 2850 TURNER 30 1500 2850 WARD 30 1250 2850
COOL! THANKS! I'm really smarter now! :)
OK and now one question yet remains - I've reached my highest level of
understanding of Oracle docs!
Where is written that without order by clause functions that actually
can use windowing clause returns value for all the partition?
The closest match for me seems these two sentences in
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/functions001.htm#i81407
"windowing_clause
[..]
You cannot specify this clause unless you have specified the
order_by_clause."
and
"query_partition_clause
Use the PARTITION BY clause to partition the query result set into
groups based on one or more value_expr. If you omit this clause, then
the function treats all rows of the query result set as a single
group."
OK probably reading these two sentences one should logically judge
that without order by clause and windowing clause the only logical
possibility is to return function for ALL the partition, but that I
can judge only now when I reread docs again and know what each
combination of partition by clause, order by clause and windowing
clause works.
Anybody can show me more clear description?
Gints Plivna
http://www.gplivna.eu
Received on Thu Apr 12 2007 - 15:22:28 CDT
 |
 |