Help with some ordering

#21

On Sep 29, 11:44*pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:

Quote:

a... (AT) mickeyschicago (DOT) com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b58... (AT) y71g2000hsa (DOT) 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

Thanks for all your info, the DENSE_RANK did the job.....

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Help with some ordering

comp.databases.oracle.misc comp.databases.oracle.misc

Re: Help with some ordering - 09-30-2008 , 07:47 AM