Cheng
That's because you haven't defined a windowing clause. If you don't
define a specific window then default window definition is 'rows between
unbounded preceding and current row'.
Precesily that's why first_value is always returning one value and
last_value is returning different value each time as current row is having a
different (higher) empno. Since emp table rows are inserted in increasing
empno column, last_value (ename) is returning current row value.
You should probably specify window clause explicitly.
1 select
2 deptno,
3 ename,
4 empno,
5 first_value(ename) over (partition by deptno order by deptno
6 rows between unbounded preceding and unbounded following )
first_emp,
7 last_value(ename) over (partition by deptno order by deptno
8 rows between unbounded preceding and unbounded following)
last_emp
9 from emp
10 where deptno = 10
11* order by deptno
RSHAMSUD @ DBATEST:ent4pdb1r> /
DEPTNO ENAME EMPNO FIRST_EMP LAST_EMP
---------- ---------- ---------- ---------- ----------
10 CLARK 100 CLARK KING
10 MILLER 300 CLARK KING
10 KING 200 CLARK KING
1 select
2 deptno,
3 ename,
4 empno,
5 first_value(ename) over (partition by deptno order by deptno,
empno
6 rows between unbounded preceding and unbounded following )
first_emp,
7 last_value(ename) over (partition by deptno order by deptno, empno
8 rows between unbounded preceding and unbounded following)
last_emp
9 from emp
10 where deptno = 10
11* order by deptno
RSHAMSUD @ DBATEST:ent4pdb1r> /
DEPTNO ENAME EMPNO FIRST_EMP LAST_EMP
---------- ---------- ---------- ---------- ----------
10 CLARK 100 CLARK MILLER
10 KING 200 CLARK MILLER
10 MILLER 300 CLARK MILLER
Thanks
Riyaj Shamsudeen
LS Cheng wrote:
Hi
What I dont understand it why ordering by deptno, empno does not change
FIRST_VALUE result but does change LAST_VALUE results.
Rgds
--
LSC
On 1/21/07, Ken Naim <kennaim@xxxxxxxxx> wrote:
>
> Yes when you order by a column combination that is not unique you can
> getn any order within that set just like with an order by within the
> set, imo you should always order by some comination that yields a
> unique combination so results are reproducible, even if the last
> column is the pk or rowid. Last value and first value produce the same
> results as long as the order by conditions are exactly flipped
> including the ordering of nulls.
>
> On 1/20/07, LS Cheng <exriscer@xxxxxxxxx> wrote:
> > 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
>
The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material. If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited. If you are not the intended
recipient, please contact the sender and delete the material from any
computer.
