You can use the group by and having clause:
use test;
drop table if exists duptest;
create table duptest(
x int
, z varchar(25)
) type=innodb;
insert into duptest(x, z) values(1, 'Val1');
insert into duptest(x, z) values(2, 'Val2');
insert into duptest(x, z) values(3, 'Val3');
insert into duptest(x, z) values(4, 'Val3');
insert into duptest(x, z) values(5, 'Val3');
insert into duptest(x, z) values(6, 'Val4');
insert into duptest(x, z) values(7, 'Val4');
insert into duptest(x, z) values(8, 'Val5');
insert into duptest(x, z) values(9, 'Val6');
insert into duptest(x, z) values(10, 'Val6');
select z, count(*) as numrecords
from duptest
group by z
having numrecords > 1;
This shows us that Val3, Val4, and Val6 are duplicated. You can leave
off the having clause and order by and see the counts of all of them (I
added an order by so that the ones with 1 show up first) as follows:
select z, count(*) as numrecords
from duptest
group by z
order by numrecords;
hth,
Dan
-----Original Message-----
From: Randy Chrismon [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 21, 2003 12:15 PM
To: [EMAIL PROTECTED]
Subject: Find non-unique values
I have a table wherein one column is SUPPOSED to be unique but I
strongly suspect isn't. Because I had this suspicion, I did not apply
a unique index to the column. Assuming there are no null values in
that column how would I find the instances of non-unique values? If I
try to alter table add unique... will it abort if the values are
non-unique, or will it drop the non-unique rows? (MyISAM tables).
There is a valid primary key on the table but (obviously) it does not
include the column in question.
Also, as this is really a SQL question (I think), is there a good
mailing list, forum, or web site devoted to SQL questions --
particularly the dialect of SQL spoken by MySQL?
Thanks.
Randy
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
