Re: Group By?

select customer_id, end_date, subscr_type from (

   SELECT co.customer_id c_id, engine.calc_end_date (co.order_date, p.quantity, p.quantity_uom) end_date,

          DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial', 'Paid') subscr_type, row_number() over (partition by customer_id order by rowid) ordering

   FROM engine.customer_order co, engine.order_line ol, engine.product p, product.newsletter_subscription ns

   WHERE co.order_id = ol.order_id
   AND ol.product_id = p.product_id
   AND p.code = ns.code
   AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)    AND ol.status = 'Active'
)
where ordering=1

   ORDER BY customer_id;

<amerar_at_iwc.net> wrote in message
news:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762_at_h11g2000prf.googlegroups.com...
>
> Hi All,
>
> I have the following query. For a few records, it returns duplicate
> CUSTOMER_ID's, though the rest of the record values are different.
>
> I want 1 record per CUSTOMER_ID, regardless of the other values.
>
> SELECT co.customer_id c_id, engine.calc_end_date (co.order_date,
> p.quantity, p.quantity_uom) end_date,
> DECODE(ns.subscr_type, 'Trial', 'Trial', 'Test', 'Trial',
> 'Paid') subscr_type
> FROM engine.customer_order co, engine.order_line ol, engine.product
> p, product.newsletter_subscription ns
> WHERE co.order_id = ol.order_id
> AND ol.product_id = p.product_id
> AND p.code = ns.code
> AND p.subproduct_id IN (197, 202, 209, 216, 220, 222)
> AND ol.status = 'Active'
> ORDER BY co.customer_id;
>
> Still working a solution.
Received on Fri Feb 08 2008 - 15:16:43 CST