CODEWITHSUNDEEP
sqlsql-server
Biswanath Das
Biswanath Das

Reputation: 33

SQL query to find duplicate rows

I have table 1 which has IND_REF and CODE columns:

enter image description here

I would like to find duplicate codes.

I want to have the below image for output:

enter image description here

I would appreciate if someone could help me or guide me how I can achieve this.

Upvotes: 0

Views: 77

Answers (5)

Dinesh Sah
Dinesh Sah

Reputation: 311

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

Upvotes: 0

marcothesane
marcothesane

Reputation: 6749

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

Upvotes: 0

MC LinkTimeError
MC LinkTimeError

Reputation: 117

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

Upvotes: 0

Biswanath Das
Biswanath Das

Reputation: 33

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

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

Aggregation with GROUP BY is probably the easiest way here:

SELECT IND_REF, CODE
FROM yourTable
GROUP BY IND_REF, CODE
HAVING COUNT(*) > 1;

Upvotes: 2

Related Questions