Incorrect Query

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 Tue Jul 08 2008 - 14:09:03 CDT