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: Mon, 22 Jan 2007 08:40:13 +0100
Message-ID: <6e9345580701212340r9d299efo52670fbe66054de4@mail.gmail.com>


Hi Riyaj

Thanks for the reply, you are right first_value and last_value get the first and last value of a window. I was thinking about partition instead of window!

Thanks

--
LSC



On 1/22/07, Riyaj Shamsudeen <rshamsud_at_jcpenney.com> wrote:

>
> 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_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
> >
>
>
>
> 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.
>
>
>
-- http://www.freelists.org/webpage/oracle-l
Received on Mon Jan 22 2007 - 01:40:13 CST

Original text of this message

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