Help with some ordering

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).

Any idea why I am not getting this??

a... RemoveThis @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