List:General Discussion« Previous MessageNext Message »
From:Sasha Pachev Date:May 8 1999 7:05pm
Subject:Re: Test for duplicates
View as plain text  
Richard Skelton wrote:
> 
> Hi,
> How do I find duplicate entrys in a column.
> In the table below I know I have duplicate entries in dwg_no how do I find/list
> them?
> 
> mysql> show columns from drawing;
> +-------------------+--------------+------+-----+---------+-------+
> | Field             | Type         | Null | Key | Default | Extra |
> +-------------------+--------------+------+-----+---------+-------+
> | dwg_no            | char(8)      | YES  |     | NULL    |       |
> | speci             | char(60)     | YES  |     | NULL    |       |
> | tfdn              | char(8)      | YES  |     | NULL    |       |
> | title             | char(80)     | YES  |     | NULL    |       |
> | plant             | char(25)     | YES  |     | NULL    |       |
> | drawn             | char(8)      | YES  |     | NULL    |       |
> | sub_con           | char(10)     | YES  |     | NULL    |       |
> | project           | char(20)     | YES  |     | NULL    |       |
> | site              | char(20)     | YES  |     | NULL    |       |
> | rig               | char(20)     | YES  |     | NULL    |       |
> | date              | char(10)     | YES  |     | NULL    |       |
> | remarks           | char(30)     | YES  |     | NULL    |       |
> | prefix            | char(7)      | YES  |     | NULL    |       |
> | issue             | char(7)      | YES  |     | NULL    |       |
> | micro             | char(4)      | YES  |     | NULL    |       |
> | scanned           | char(3)      | YES  |     | NULL    |       |
> | modification_time | char(30)     | YES  |     | NULL    |       |
> | who_modified      | char(10)     | YES  |     | NULL    |       |
> | group_modified    | char(10)     | YES  |     | NULL    |       |
> | db_id             | smallint(10) | YES  |     | NULL    |       |
> +-------------------+--------------+------+-----+---------+-------+
> 20 rows in set (0.20 sec)
> 
> --
> Cheers
> Richard.
> =
> Richard Skelton      |   e-mail : Richard.Skelton@stripped

select dwg_no, count(*) as n from drawing group by dwg_bo having n > 1;

will list all dwg_no values that are duplicated.

-- 
Sasha Pachev
http://www.sashanet.com
Thread
Test for duplicatesRichard Skelton8 May
  • Re: Test for duplicatesSasha Pachev8 May