Re: Help with some ordering

From: Shakespeare <whatsin_at_xs4all.nl>
Date: Tue, 30 Sep 2008 10:59:32 +0200
Message-ID: <48e1ea76$0$199$e4fe514c@news.xs4all.nl>

"Shakespeare" <whatsin_at_xs4all.nl> schreef in bericht news:48e1ea06$0$201$e4fe514c_at_news.xs4all.nl...
>
> <art_at_mickeyschicago.com> schreef in bericht
> news:0f59e550-e2e5-4c9f-b873-3fc2d7b5846f_at_y71g2000hsa.googlegroups.com...
>>
>> 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??
>
> 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
>

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

And why you still post replies to your own post with the same question. Received on Tue Sep 30 2008 - 03:59:32 CDT

Original text of this message