I have table 1 which has IND_REF and CODE columns:
I would like to find duplicate codes.
I want to have the below image for output:
I would appreciate if someone could help me or guide me how I can achieve this.
Add a comment |
5 Answers
Aggregation with GROUP BY is probably the easiest way here:
SELECT IND_REF, CODE FROM yourTable GROUP BY IND_REF, CODE HAVING COUNT(*) > 1; 
1 Comment
Biswanath Das
Indeed. Thank you for your answer, I have figured it out too. Much appriciated your time.
I was able to achieve this using having clause.
SELECT IL.IND_REF,IL.CODE FROM TABLE1 IL WHERE IL.TYPE=12 group by IL.IND_REF,IL.CODE having COUNT(*)>1 1 Comment
Biswanath Das
@DaleK I tried to accept this but as the answer was less then 5 mins it didnt allowed me, i know that. I have accepted his answer.
I think the fastest way is to select on count:
SELECT [IND_REF], [CODE] FROM Table1 GROUP BY [IND_REF] // add [CODE] if u like different codes to be mapped to the same [IND_REF] HAVING COUNT(*) > 1 should do the work
Comments
Always bring your sample data as text - so that we can copy-paste it into one or more SQL statements
Like here, where I re-typed it by hand:
WITH input(ind_ref,code) AS ( SELECT 1234,12 UNION ALL SELECT 1234,13 UNION ALL SELECT 1222,12 UNION ALL SELECT 1222,11 UNION ALL SELECT 1333,12 UNION ALL SELECT 1333,12 UNION ALL SELECT 1333,13 ) -- then, as @Biswanath Das did, continue like this: SELECT ind_ref , code FROM input GROUP BY ind_ref , code HAVING COUNT(*) > 1; returning:
ind_ref | code ---------+------ 1333 | 12 
Comments
In this case we can use "GROUP BY" and "HAVING"
SELECT IND_REF, CODE, COUNT(IND_REF) as duplicate_count FROM `table_name` GROUP BY IND_REF HAVING duplicate_count > 1 