List:General Discussion« Previous MessageNext Message »
From:Scott Hamm Date:April 5 2005 3:14pm
Subject:Re: Duplicated records
View as plain text  
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
> 
> 


-- 
Power to people, Linux is here.
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