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?
 
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
 
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
awking00Commented:
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
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.