Re: Help with some ordering

<art_at_mickeyschicago.com> a écrit dans le message de 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??

Have a look at ROW_NUMBER/RANK/DENSE_RANK functions.

Regards
Michel Received on Mon Sep 29 2008 - 23:44:12 CDT