Re: Help with some ordering

From: Shakespeare <>
Date: Tue, 30 Sep 2008 10:57:40 +0200
Message-ID: <48e1ea06$0$201$>

<> schreef in bericht
> 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
> 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??

No, and I don't have any idea why you keep posting questions without following up to most of the responses you get. And why you keep changing your address with every n-th post, unless this is related to the none-responsiveness.

Shakespeare Received on Tue Sep 30 2008 - 03:57:40 CDT

Original text of this message