Re: Help with some ordering

On Sep 29, 11:44 pm, "Michel Cadot" <micadot{at}altern{dot}org> wrote:
> <a..._at_mickeyschicago.com> a écrit dans le message de news: 0f59e550-e2e5-4c9f-b873-3fc2d7b58..._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

Thanks for all your info, the DENSE_RANK did the job..... Received on Tue Sep 30 2008 - 07:47:07 CDT