Re: Incorrect Query

I think you should add a "DISTINCT" keyword in your SQL. "Mtek" <mtek_at_mtekusa.com> wrote in message news:3447443a-caa3-4f65-860d-015e85cb9552_at_s50g2000hsb.googlegroups.com...
>
> 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.
Received on Wed Jul 09 2008 - 02:14:40 CDT