Re: Group By?

On Feb 8, 2:45 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

I'll give that a try Dave. As always, thank you. I did try this for about 2 hours with no avail...... Received on Sat Feb 09 2008 - 12:09:24 CST