Help with some ordering

From: <art_at_mickeyschicago.com>
Date: Mon, 29 Sep 2008 13:33:43 -0700 (PDT)
Message-ID: <0f59e550-e2e5-4c9f-b873-3fc2d7b5846f@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?? Received on Mon Sep 29 2008 - 15:33:43 CDT

Original text of this message