Re: analytics first_value and last

From: Riyaj Shamsudeen <rshamsud_at_jcpenney.com>
Date: Sun, 21 Jan 2007 18:33:50 -0600
Message-ID: <45B4066E.5050103@jcpenney.com>

('binary' encoding is not supported, stored as-is)

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 <mailto: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
> <mailto: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 Sun Jan 21 2007 - 18:33:50 CST

Re: analytics first_value and last_value