Re: Incorrect Query

On Jul 8, 8:09 pm, Mtek <m..._at_mtekusa.com> wrote:
> Hi,
>
> I'm trying to use an analytic function to get the first row in a group
> ordered by a date.
>
> SELECT customer_id, action,
> FIRST_VALUE(TRUNC(action_date))
> OVER (PARTITION BY customer_id
> ORDER BY action_date DESC) AS LAST_DATE
> FROM email_product_hist
> ORDER BY customer_id;
>
> Customer ID Action Action Date
> 413 D 29-AUG-07
> 413 D 29-AUG-07
> 416 D 09-SEP-06
> 416 D 09-SEP-06
> 416 A 09-SEP-06
> 416 D 09-SEP-06
> 416 A 09-SEP-06
> 416 D 09-SEP-06
> 421 D 15-APR-08
> 443 D 04-MAY-04
>
> Now, I have truncated the date, so the minutes should not matter.
> But, because I use FIRST_VALUE and I am partitioning on the customer
> ID, I thought it would basically group it by customer ID and then get
> the first record, as the group is ordered by date DESC.
>
> But, I am getting duplicated, which I should not be. Anyone see what
> is wrong with my query?
>
> Always thanks!
>
> John.

Its non-deterministic (I think thats the word) i.e. it returns all the rows in your set showing the first_value figure against each one in the group.

Consider a version using scott.emp:

select deptno, sal, first_value(ename)
  over (partition by deptno order by sal asc) as fv     from (select * from emp

            order by empno desc)

It doesn't group it doesn't aggregate it returns all the rows in the set the fv column just shows the first value in the partitioned group each time.

DEPTNO SAL FV

---------     ---------  ----------
       10      1300 MILLER
       10      2450 MILLER
       10      5000 MILLER
       20       800  SMITH
       20      1100 SMITH
       20      2975 SMITH
       20      3000 SMITH
       20      3000 SMITH
       30       950  JAMES
       30      1250 JAMES
       30      1250 JAMES
       30      1500 JAMES
       30      1600 JAMES
       30      2850 JAMES

You get the idea? Perhaps you want a group by clause in their somewhere.

Cheers Received on Wed Jul 09 2008 - 06:08:43 CDT