CODEWITHSUNDEEP
mysqlselectcountduplicatesdistinct
R_User
R_User

Reputation: 11082

Find duplicates for combination of two columns in a MySQL database

I work on a dataset with three different columns: pile, position and info.

There is no duplicate in the database, but it can happen, that for one combination of pile and position there is one or two different texts in the info column. And those are the entries I tried to find.

I tried the following

SELECT COUNT(DISTINCT(`pile`, `position`)) FROM db;

But received an error message

ERROR 1241 (21000): Operand should contain 1 column(s)

Is there a way to find distinct combinations of values in two columns?

Upvotes: 15

Views: 17547

Answers (3)

John Garreth
John Garreth

Reputation: 1132

This works even without subselects.

SELECT
  `pile`,
  `position`,
  COUNT(*) AS c
FROM
  db
GROUP BY
  `pile`,
  `position`
HAVING c > 1;

The command above shows all combinations of pile and position that occur more than once in the table db.

Upvotes: 35

wildplasser
wildplasser

Reputation: 44250

SELECT *
FROM db x
WHERE EXISTS (
  SELECT 1 FROM db y
  WHERE y.pile = x.pile
  AND y.position =x.postion
  AND y.other_field <> x.other_field
  );

Now, for other_field you can use some unique id column, or any combination of fields (except for {pole, postion} of course)

Upvotes: 0

RichardTheKiwi
RichardTheKiwi

Reputation: 107766

To get the count of distinct duplicates (group by used in preference here)

   select count(*)
     from (
   select pile, position
     from db
 group by pile, position
          ) x

To find the actual duplicate records

   select db.*
     from (
   select pile, position
     from db
 group by pile, position
   having count(*) > 1
          ) x
     join db on db.pile = x.pile and db.position = x.position

Upvotes: 0

Related Questions