List:General Discussion« Previous MessageNext Message »
From:Peter Brawley Date:April 5 2005 5:29pm
Subject:Re: Duplicated records
View as plain text  
Scott,

To find dupe batch values

SELECT batch, COUNT(batch) AS Cnt
FROM qa
GROUP BY batch
HAVING Cnt > 1;

Peter Brawley
http://www.artfulsoftware.com

-----


Scott Hamm wrote:

>Sorry for the confusion.  I don't mean a duplicated records, but to
>find duplicated Batch where two different operators worked on a single
>batch (one started off, then another one to finish the batch) and a
>single reviewer to review a batch. I need a list that lists duplicated
>Batches with different operators that worked on that batch.
>
>For example: (for clarification)
>SELECT ID, Batch, KeyDate, OperatorID, QAID, NTID 
>FROM QA 
>WHERE Batch='439584414';
>
>ID         Batch              KeyDate                      OperatorID
>QAID      NTID
>90577    439584414	2004-10-03 00:00:00	2661	      5334	JulieAnt	
>90575    439584414	2004-10-03 00:00:00	5657	      5334	JulieAnt	
>
>
>On Apr 5, 2005 10:54 AM, Rhino <rhino1@stripped> wrote:
>  
>
>>----- Original Message -----
>>From: "Scott Hamm" <linuxgold@stripped>
>>To: "Mysql" <mysql@stripped>
>>Sent: Tuesday, April 05, 2005 10:05 AM
>>Subject: Duplicated records
>>
>>    
>>
>>>Here is my novice question:
>>>I need to find duplicates in "Batch" category when I issued this query:
>>>
>>>SELECT * FROM QA WHERE Batch=439584414;
>>>
>>>Result:
>>>90577
>>>1 2661 5334 JulieAnt 25 5 5 439584414 2004-10-03 00:00:00 2004-10-03
>>>00:00:00 0 90575
>>>1 5657 5334 JulieAnt 25 5 5 439584414 2004-10-03 00:00:00 2004-10-03
>>>      
>>>
>>00:00:00 0
>>    
>>
>>>How do I issue a query that finds duplicated Batch number?
>>>
>>>      
>>>
>>First of all, I think you made a mistake when putting this data into your
>>note, a mistake which is compounded by the wrapping that the email program
>>did. I *think* you meant to display two rows with the '90577' value at the
>>end of the second row, not before the first row. This makes the example a
>>bit confusing and hard to follow.
>>
>>The two rows you provide in your example are not duplicates of each other.
>>Even assuming that the '90577' actually belongs at the end of the second row
>>rather than the start of the first row, the two rows are different: the
>>first row ends in '90575', not '90577' and the second value in each row is
>>also different: '2661' and '5657'. Therefore, you're not really trying to
>>find duplicate records because a duplicate row would be one that is
>>identical in every column, not just identical in several columns. I hope
>>that doesn't sound like I am splitting hairs; maybe I am. ;-)
>>
>>Anyway, the standard method for finding duplicates of rows goes like this
>>and can be adapted for your situation. Let's say that you have a table
>>called Foo with columns foo1, foo2, and foo3 and you want to find all of the
>>duplicate rows in the table, i.e. any row whose foo1, foo2, and foo3 value
>>is identical to the foo1, foo2, and foo3 value of another table. You would
>>write this query:
>>
>>    select foo1, foo2, foo3, count(*) as num
>>    from Foo1
>>    group by foo1, foo2, foo3
>>    having num > 1
>>    order by num;
>>
>>Your result might look like this:
>>
>>    foo1    foo2    foo3    num
>>    Daffy    D        Duck    1
>>    Bugs    B        Bunny    1
>>    Elmer    A        Fudd    3
>>
>>This result means that Daffy D Duck and Bugs B Bunny each occur once in the
>>table but Elmer A Fudd occurs three times.
>>
>>In the case you mentioned in your email, where the rows aren't complete
>>duplicates of one another, just write the query so that you omit the columns
>>which are allowed to be different. In your case, that would appear to be all
>>but the second and last columns.
>>
>>However, I think if you get to the point of having duplicate records in a
>>table when you don't want them, finding them is like shutting the barn door
>>after the horse is already gone. I think you need to reconsider your design,
>>specifically your primary key, and change that primary key to *PREVENT* the
>>duplicate keys in the first place.
>>
>>For instance, in the case of the Foo1 table, I can prevent duplicate rows by
>>choosing a good primary key; in this case I would choose the combination of
>>all three columns in the table; that would ensure that I never stored more
>>than 1 person named Elmer A Fudd. [Actually, that's not a great example! It
>>might be perfectly valid to have two people named Elmer A Fudd - or John A
>>Smith - so I probably need to add additional columns to my table to ensure
>>that the rows are unique; something like Social Security Number which is
>>(supposed to be) unique would be ideal for this purpose.]
>>
>>Rhino
>>
>>--
>>No virus found in this outgoing message.
>>Checked by AVG Anti-Virus.
>>Version: 7.0.308 / Virus Database: 266.9.2 - Release Date: 05/04/2005
>>
>>
>>    
>>
>
>
>  
>

Attachment: [text/html]
No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.9.3 - Release Date: 4/5/2005
Thread
Duplicated recordsScott Hamm5 Apr
  • Re: Duplicated recordsJeremy Cole5 Apr
  • Re: Duplicated recordsRhino5 Apr
    • Re: Duplicated recordsScott Hamm5 Apr
      • Re: Duplicated recordsPeter Brawley5 Apr
  • Re: Duplicated recordsRhino5 Apr
    • Re: Duplicated recordsScott Hamm5 Apr
  • Re: Duplicated recordsRhino5 Apr