Incorrect Query
Date: Tue, 8 Jul 2008 12:09:03 -0700 (PDT)
Message-ID: <3447443a-caa3-4f65-860d-015e85cb9552@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 Tue Jul 08 2008 - 14:09:03 CDT