Re: Group By?

Comments embedded.
On Feb 8, 1:21 pm, "ame..._at_iwc.net" <ame..._at_iwc.net> wrote:
> 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.
>

So the query is returning distinct records.

> I want 1 record per CUSTOMER_ID, regardless of the other values.

Why? What is the purpose of that requirement?

>
>    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.

Possibly you haven't tried hard enough:

select c_id, max(end_date), max(subscriber_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

   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' )
group by c_id
order by c_id;

The query above should provide you one record per customer id; how useful that data will be remains to be seen.

David Fitzjarrell Received on Fri Feb 08 2008 - 14:45:24 CST