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
