Re: analytics first_value and last_value

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_at_gmail.com> 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_at_gmail.com> 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

>


--
http://www.freelists.org/webpage/oracle-l