analytics first_value and last_value

Hi

I am writing a query using analytics. I am testing first_value and last_value functions. I am having trouble what is the difference between these two functions. I thought they do the samething but just the another way round however this query

select

   deptno,
   ename,
   first_value(ename) over (partition by deptno order by deptno, empno) first_emp,

   last_value(ename) over (partition by deptno order by deptno, empno) last_emp
from emp
where deptno = 10
order by deptno

    DEPTNO ENAME FIRST_EMP LAST_EMP
---------- ---------- ---------- ----------

        10 CLARK      CLARK      CLARK
        10 KING       CLARK      KING
        10 MILLER     CLARK      MILLER

and this query:

select

   deptno,
   ename,
   first_value(ename) over (partition by deptno order by deptno, empno) first_emp,

   last_value(ename) over (partition by deptno order by deptno) last_emp from emp
where deptno = 10
order by deptno

    DEPTNO ENAME FIRST_EMP LAST_EMP
---------- ---------- ---------- ----------

        10 CLARK      CLARK      MILLER
        10 KING       CLARK      MILLER
        10 MILLER     CLARK      MILLER

Show different result in last_emp column but simply because in the first query, last_value clause I added an order by deptno, empno and in the second query I susbstituted by order by deptno. But that doesnt seem to affect first_value?

Am I missing something?

TIA
--

LSC
--

http://www.freelists.org/webpage/oracle-l Received on Sat Jan 20 2007 - 17:19:59 CST