Re: Incorrect Query

On 8 jul, 21:09, 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.

You may try:

SELECT customer_id, action, action_date
  FROM ( SELECT customer_id, action, action_date, RANK() OVER (PARTITION BY customer_id ORDER BY action_date DESC) MY_RANK

          FROM email_product_hist
       )

(Not tested: no Oracle at hand)

HTH Cheers.

Carlos. Received on Wed Jul 09 2008 - 10:12:15 CDT