Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: analytics first_value and last_value

Re: analytics first_value and last_value

From: LS Cheng <exriscer_at_gmail.com>
Date: Sun, 21 Jan 2007 11:51:03 +0100
Message-ID: <6e9345580701210251v760c6fb5r48ddb250fe21931c@mail.gmail.com>


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
Received on Sun Jan 21 2007 - 04:51:03 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US