List:General Discussion« Previous MessageNext Message »
From:Dirk Bremer Date:May 1 2006 6:06pm
Subject:Finding duplicates, etc.
View as plain text  
I have the following table:

*Column Information For - production.group15034_I*/
----------------------------------------------------

Field        Type                       Collation          Null    Key
Default     Extra           Privileges                       Comment
-----------  -------------------------  -----------------  ------
------  ----------  --------------  -------------------------------
-------
ident        int(11)                    NULL                       PRI
(NULL)      auto_increment  select,insert,update,references         
account      int(10) unsigned zerofill  NULL
0000000000                  select,insert,update,references         
sub_account  tinyint(3) unsigned        NULL
0                           select,insert,update,references         
address      varchar(132)               latin1_swedish_ci
select,insert,update,references         
data         text                       latin1_swedish_ci
select,insert,update,references         

/*Index Information For - production.group15034_I*/
---------------------------------------------------

Table         Non_unique  Key_name  Seq_in_index  Column_name  Collation
Cardinality  Sub_part  Packed  Null    Index_type  Comment
------------  ----------  --------  ------------  -----------  ---------
-----------  --------  ------  ------  ----------  -------
group15034_I           0  PRIMARY              1  ident        A
0    (NULL)  (NULL)          BTREE              

/*DDL Information For - production.group15034_I*/
-------------------------------------------------

Table         Create Table

------------
----------------------------------------------------------------------
group15034_I  CREATE TABLE `group15034_I` (

                `ident` int(11) NOT NULL auto_increment,

                `account` int(10) unsigned zerofill NOT NULL default
'0000000000',  
                `sub_account` tinyint(3) unsigned NOT NULL default '0',

                `address` varchar(132) NOT NULL default '',

                `data` text NOT NULL,

                PRIMARY KEY  (`ident`)

              ) ENGINE=MyISAM DEFAULT CHARSET=latin1


The account and address fields will contain duplicate values under
certain scenarios. I have the following 3 requirements:

1. Select the accounts that have unique accounts and addresses, i.e. not
duplicate:
select ident,account,address,count(*) as N,data from group15034_I group
by account,address having N = 1
This select appears to work fine.

2. Select the acocunts that have both duplicate accounts and addresses:
select account,address,count(*) as N from group15034_I group by
account,address having N > 1
This select appears to work fine.

3. I want the exceptions to the above two conditions. Specifically, I
would like to know where there ar duplicate accounts that do not have
duplicate addresses. I think that at this point in time that these are
included in the first query. I would like to separate these out to
report on them.

I can't figure out a way to accomplish number 3 but know that in the
given data set that this condition does occur.

Dirk Bremer - Senior Systems Engineer - ESS/AMS - NISC Lake St. Louis MO
- USA Central Time Zone
636-755-2652 fax 636-755-2503

dirk.bremer@stripped
www.nisc.coop
Thread
Finding duplicates, etc.Dirk Bremer1 May
  • Re: Finding duplicates, etc.Peter Brawley1 May
RE: Finding duplicates, etc.Dirk Bremer2 May