Re: Help with some ordering
Date: Mon, 29 Sep 2008 21:42:44 -0700 (PDT)
> I have this query:
> SELECT customer_id, co.order_id, p.product_id, ol.status,
> p.code, p.subproduct_id,
> OVER (PARTITION BY customer_id, subproduct_id
> ORDER BY order_date
> DESC) LAST_STATUS
> FROM engine.customer_order co, engine.order_line ol,
> engine.product p
> WHERE co.order_id = ol.order_id
> AND ol.product_id = p.product_id
> AND p.subproduct_id IN (209, 211, 216, 217, 220, 222,
> 223, 224, 226)
> ORDER BY customer_id;
> I want to get the status of the most recent record for
> that customer.
> There can be more than 1 record for that customer with
> the same subproduct ID.
> A customer may have ordered a product, canceled it and
> re-ordered it at a later date.....I would want the
> status of the most recent, along with the other
> information above.
> This query is giving me all the records. I thought
> this query would partition by customer ID / subproduct
> ID, order by the order date, and give me the first
> record (FIRST_VALUE).
The analytical function _is_ partitioning and giving you the first status within that partition by the the order date.
> Any idea why I am not getting this??
Analytic functions don't reduce rows. Indeed they give you group information for _every_ row.
Select the status as normal, but use row_number() instead of first_value. Then do an outer select on your query looking for row numbers of 1.
Note that this might still give you multiple rows if a customer orders the same product multiple times on the same order_date. In which case it's generally best to refine the order by in the row_number(), e.g. by order_id desc.
-- PeterReceived on Mon Sep 29 2008 - 23:42:44 CDT