mahjag
asked on
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_
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?
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_
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?
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
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
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?
>>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?
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
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
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
select count(*) Rows,cust_id from tableA group by cust_id having count(*) > 1
Mark,
Isn't that what I posted in the very first post?
Isn't that what I posted in the very first post?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Oops! Sorry, slightvw, I didn't read your first comment carefully.
Congrats to the Mountaneers (for last night's big Orange Bowl victory).
Congrats to the Mountaneers (for last night's big Orange Bowl victory).
for a cust_id with a count > 1:
select count(*),cust_id from tableA group by cust_id having count(*) > 1