Re: Incorrect Query

On Jul 9, 6:08 am, Doug T <dougtmur..._at_hotmail.com> wrote:
> 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- Hide quoted text -
>
> - Show quoted text -

Thanks for the feedback......got it to work! Received on Wed Jul 09 2008 - 12:58:33 CDT