CODEWITHSUNDEEP
mysql
Programster
Programster

Reputation: 12764

MySQL - Check Uniqueness of Columns Values

Is it possible to write a MySQL query to check the "uniquness" of a combination of columns?

At the moment I have been doing this by attempting to create unique key on the columns in phpmyadmin and seeing if it fails, which is not ideal for lots of reasons.

A query could allow me to find the rows that have the duplicate values in those columns or even count how many rows are causing the combination to not be unique. It would also be nice to know how many different sets of duplicate values there are.

Example with checking columns b, c

a|b|c
-----
1|2|3
4|1|3
1|2|9

no rows for above returned even though two values are the same in b and two values are the same in c.

a|b|c
-----
1|2|3
4|5|6
7|2|3
7|5|6

All rows would be returned or the query could return the sets 2|3 and 5|6 .

Upvotes: 5

Views: 3273

Answers (2)

cyon
cyon

Reputation: 9538

A select query with group by a,b,c and having count(*) > 1 will show you rows which are duplicates of other rows. To see the unique rows you can change the having clause tohaving count(*) = 1.

If you want to check which combinations of b,c are unique you can do

select b,c
from t1
group by b,c
having count(*) = 1

For example to find duplicate a,b,c combinations you can do:

select a,b,c
from t1
group by a,b,c
having count(*) > 1

Upvotes: 9

num8er
num8er

Reputation: 19372

You better create unique table like this and then rename as You wish:

CREATE TABLE table_unique AS 
(SELECT a,b,c FROM table GROUP BY a,b,c HAVING COUNT(*)=1);

Upvotes: 0

Related Questions