CODEWITHSUNDEEP
mysqlsqljoin
qwerty
qwerty

Reputation: 897

Sql Query to find duplicates in 2 columns where the values in first column are same

I have a table where the first column contains States and second column contains Zip Code. I want to find duplicate Zip Codes in the same State. So, the first column can have same values but i need to find the duplicates in the second column that have the same values in the first column.

Table :

+---+----+------+
| Z | A  |  B   |
+---+----+------+
| 1 | GA | 1234 |
| 2 | GA | 321  |
| 3 | GA | 234  |
| 4 | GA | 9890 |
| 5 | GA | 1234 |
+---+----+------+

The query should return the value of the zip code that has a duplicate i.e 1234. I have around 10000+ records.

Thank You.

Upvotes: 0

Views: 1057

Answers (5)

Jim Macaulay
Jim Macaulay

Reputation: 5165


You can use below query.

SELECT A, B, COUNT(*)
FROM TABLE_NAME
GROUP BY A, B
HAVING COUNT(*) > 1;

Upvotes: 0

Josh Gilfillan
Josh Gilfillan

Reputation: 5146

It sounds like you want both rows returned where duplicates are found. This should work:

with cte1 as (
select
   A
  ,B
  ,count(1) over (partition by A, B) as counter
from table_name
)
select
   A
  ,B
from cte1
where 1=1
  and counter > 1
order by A, B
;

If you want to know how many duplicate rows there are in total, you can select the "counter" field in the final select:

with cte1 as (
select
   A
  ,B
  ,count(1) over (partition by A, B) as counter
from table_name
)
select
   A
  ,B
  ,counter
from cte1
where 1=1
  and counter > 1
order by A, B
;

Upvotes: 0

Vijunav Vastivch
Vijunav Vastivch

Reputation: 4211

try this:

select A,B, count(CONCAT_WS('',A,B)) as cnt  from
(select * from yourtable) as a  group by A,B having count(CONCAT_WS('',A,B))>1

result for all duplicate records or more than one records:

GA  1234    2

Upvotes: 0

toonice
toonice

Reputation: 2246

Please try the following...

SELECT Z AS RecordNumber,
       tblTable.A AS State,
       tblTable.B AS ZipCode
FROM tblTable
JOIN ( SELECT A,
              B
       FROM tblTable
       GROUP BY A,
                B
       HAVING COUNT( * ) > 1
     ) AS duplicatesFinder ON tblTable.A = duplicatesFinder.A
                          AND tblTable.B = duplicatesFinder.B
ORDER BY tblTable.A,
         tblTable.B,
         Z;

This statement starts with a subquery that selects every unique combination of State and Zip Code that occurs more than once in the source table (which I have called tblTable in the absence of the table's name).

The results of this subquery are then joined to the source table based on shared values of State and Zip Code. This JOIN effectively eliminates all records from the source table that have a unique State / Zip Code combination from our results dataset.

The list of duplicated States / Zip Codes is then returned along with the values of Z associated with each pairing.

If you have any questions or comments, then please feel free to post a Comment accordingly.

Appendix

My code was tetsted against a database created using the following script...

CREATE TABLE tblTable
(
    Z   INT,
    A   CHAR( 2 ),
    B   INT
);
INSERT INTO tblTable ( Z,
                       A,
                       B )
VALUES ( 1, 'GA', 1234 ),
       ( 2, 'GA', 321 ),
       ( 3, 'GA', 234 ),
       ( 4, 'GA', 9890 ),
       ( 5, 'GA', 1234 );

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522797

Try using a GROUP BY query and retain zip codes appearing in duplicate.

SELECT A, B
FROM yourTable
GROUP BY A, B
HAVING COUNT(*) > 1

Note that we can group by state and zip code assuming that a given zip code only appears once, for a given state.

Upvotes: 1

Related Questions