Re: Help with some ordering

a..._at_mickeyschicago.com wrote:
> I have this query:
>
> SELECT customer_id, co.order_id, p.product_id, ol.status,
> p.code, p.subproduct_id,
>   FIRST_VALUE(ol.status)
>   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.

--
Peter