 | |
#21
 
| |||
| |||
|
Re: Group By? -
02-09-2008
, 12:09 PM
|
Comments embedded. On Feb 8, 1:21*pm, "ame... (AT) iwc (DOT) net" <ame... (AT) iwc (DOT) 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 |
about 2 hours with no avail......
#22
| |||
| |||
|
|
On Feb 8, 2:53*pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ame... (AT) iwc (DOT) net> schreef in berichtnews:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) 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. Then DON'T select the other values (if you don't care about them).... Shakespeare- Hide quoted text - - Show quoted text - Actually I do need them, as they are used later in another query....... |
different end dates and subscr_types but you only choose one of them
with no regard for which one you choose how can the other columns be
of any value?
#23
| |||
| |||
|
|
On Feb 8, 2:53*pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ame... (AT) iwc (DOT) net> schreef in berichtnews:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) 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. Then DON'T select the other values (if you don't care about them).... Shakespeare- Hide quoted text - - Show quoted text - Actually I do need them, as they are used later in another query....... |
different end dates and subscr_types but you only choose one of them
with no regard for which one you choose how can the other columns be
of any value?
#24
| |||
| |||
|
|
On Feb 8, 2:53*pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ame... (AT) iwc (DOT) net> schreef in berichtnews:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) 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. Then DON'T select the other values (if you don't care about them).... Shakespeare- Hide quoted text - - Show quoted text - Actually I do need them, as they are used later in another query....... |
different end dates and subscr_types but you only choose one of them
with no regard for which one you choose how can the other columns be
of any value?
#25
| |||
| |||
|
|
On Feb 8, 2:53*pm, "shakespeare" <what... (AT) xs4all (DOT) nl> wrote: ame... (AT) iwc (DOT) net> schreef in berichtnews:1c04d8cf-f3da-4b8b-9b0d-8e451c94c762 (AT) h11g2000prf (DOT) 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. Then DON'T select the other values (if you don't care about them).... Shakespeare- Hide quoted text - - Show quoted text - Actually I do need them, as they are used later in another query....... |
different end dates and subscr_types but you only choose one of them
with no regard for which one you choose how can the other columns be
of any value?
 |
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.