write a query to find dups for distinct set

I have following records in the table - Table A (where duplicate records found)

Cust_id    Prod_id       Prod_name  cust_rel_id
1-A          1-P1            Computer     1-R1
1-A           1-P1           Computer     1-R1
1-A            1-P2           Comp.Acc   1-R2
1-A           1-P2             Comp-Acc  1-R2
...
..

and so on for other products and cust_rel_ids..
if I do the following query
select count(*),cust_rel_id,cust_id from tableA group by cust_rel_id, cust_id having count(*) > 1
count(*)    cust_rel_id   cust_id
2                 1-R1            1-A
2                 1-R2            1-A

but I only want 1 row for cust_id 1-A that has duplicated rows for cust_rel_id  and if there is another cust_id that had duplicate then in that case also I want only 1 row how do I restrict my query to return only 1 row in that case?
mahjagAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
I'm not understanding.  Please post expected results.

for a cust_id with a count > 1:
select count(*),cust_id from tableA group by cust_id having count(*) > 1
0
sventhanCommented:
try to use analytical function like this....

select cust_rel_id,cust_id
from
(
select cust_rel_id,cust_id,OVER (PARTITION BY cust_rel_id ORDER BY cust_rel_id )  rn
 from tableA
)
where rn = 1
0
awking00Information Technology SpecialistCommented:
From your example,
>>how do I restrict my query to return only 1 row in that case?<<
Which row would you want to restrict your results to and why?
0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Christoffer SwanströmPartnerCommented:
If you only want one row for each cust_id then you have to do something like this:

SELECT
  cust_id
  ,COUNT(1) AS cnt_all
  ,COUNT(DISTINCT prod_name) AS cnt_dist_prod
-- add any other summary fields you want per customer id
FROM
  tableA
GROUP BY
  cust_id
0
Mark GeerlingsDatabase AdministratorCommented:
If you only want 1 row for cust_id 1-A, then in your "group by" and "having" clause make sure that include only cust_id like this:

select count(*) Rows,cust_id from tableA group by cust_id having count(*) > 1
0
slightwv (䄆 Netminder) Commented:
Mark,
Isn't that what I posted in the very first post?
0
SharathData EngineerCommented:
sventhan -  I think you missed the analytical function.
select cust_rel_id,cust_id
from
(
select cust_rel_id,cust_id,ROW_NUMBER () OVER (PARTITION BY cust_rel_id ORDER BY cust_rel_id )  rn
 from tableA 
)
where rn = 1

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Mark GeerlingsDatabase AdministratorCommented:
Oops!  Sorry, slightvw, I didn't read your first comment carefully.

Congrats to the Mountaneers (for last night's big Orange Bowl victory).
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.