Results 1 to 11 of 11
  1. #1
    Join Date
    Jul 2003
    Posts
    123

    Unanswered: Select duplicate rows

    I get a tabseparated textfile with data every friday. Faulty rows are to be returned to the source.

    Double rows are considered a fault. How do I select all double rows?

    I join the data later on with four columns so if these columns are alike the rows are considered identical even if other columns are not. Ie:

    A, B, C, D, 12, 34, 48, 76
    A, B, C, D, 23, 45, 56, 99

    These two rows are considered alike since I join on A+B+C+D.

    I'd like to select ALL double rows and insert them into a separate table that I can return to the source. Does anyone have an idea how to do this?

  2. #2
    Join Date
    Sep 2003
    Location
    The extremely Royal borough of Kensington, London
    Posts
    778
    Select * into table2
    from table1
    group by columns
    having count(*) > 1;
    Bessie Braddock: Winston, you are drunk!
    Churchill: And Madam, you are ugly. And tomorrow, I'll be sober, and you will still be ugly.

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Something like...

    Code:
       SELECT * 
         FROM myTable99 l
    LEFT JOIN (SELECT A,B,C,D 
    	     FROM myTable99 
    	 GROUP BY A,B,C,D
    	   HAVING COUNT(*) > 1) r
    	 ON l.A = r.A
    	AND l.B = r.B
    	AND l.C = r.C
    	AND l.D = r.D
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Jul 2003
    Posts
    123
    I have now made my query to look something like this:

    INSERT INTO data_return_to_COMPANY
    SELECT a.[DATE], a.CHANGE_CODE, a.AGREEMENT_ID, a.SUP_VIA_NO, a.SUPPLIER_NAME, a.AGR_DATE_FROM, a.AGR_DATE_TO, a.TOPAYMENT, a.TODELIVERY, a.DEL_LAND,
    a.DEL_PLACE, a.CURRENCY, a.LOGG_CLEAR, a.DELIVERY_PLAN, a.[COMPANY PART NUMBER], a.[PART NUMBER REVISION],
    a.[COMPANY PART NUMBER INCL REVISION], a.MAN_VIA_NO, a.PRICE_PER, a.[AGREED PRICE], a.[MANUFACTURE NAME], a.[SUPPLIER PART NUMBER], a.UNIT,
    a.PACKAGE_QTY, a.ORIGIN, a.FACTORY, a.QTY, a.ACTIVE_STATUS, a.MINORDQTY, a.LEAD_TIME, a.LEAD_TIME_WITH_FORECAST, a.SUPPLIER_SPLIT, a.[COMPANY STANDARD/UNIQUE],
    a.[QTY/VOL], a.[QTYFROM/DATEFROM], a.[QTYTO/DATETO], a.PRICE, 'Double rows'
    FROM data_file a
    LEFT JOIN (SELECT [AGR_DATE_FROM],[AGR_DATE_TO],[COMPANY PART NUMBER INCL REVISION],[FACTORY],[QTYFROM/DATEFROM],[QTYTO/DATETO]
    FROM CON_data_file
    group by [AGR_DATE_FROM],[AGR_DATE_TO],[COMPANY PART NUMBER INCL REVISION],[FACTORY],[QTYFROM/DATEFROM],[QTYTO/DATETO]
    having count(*) > 1) b
    ON
    (a.[AGR_DATE_FROM] = b.[AGR_DATE_FROM] AND
    a.[AGR_DATE_TO] = b.[AGR_DATE_TO] AND
    a.[COMPANY PART NUMBER INCL REVISION] = b.[COMPANY PART NUMBER INCL REVISION] AND
    a.[FACTORY] = b.[FACTORY] AND
    a.[QTYFROM/DATEFROM] = b.[QTYFROM/DATEFROM] AND
    a.[QTYTO/DATETO] = b.[QTYTO/DATETO])

    --------------------

    If I run the inner question it selects the 139 rows that have multiple occurences... But when I run the whole query it selects ALL rows in
    "data_file" and inserts into "data_return_to_COMPANY". I can't figure out where the fault is. Anyone?

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    What's the key of CON_DATA_FILE...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Brett: May be a dumb question, but the coffee has not taken hold, yet. Why the left join?

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    Brett: May be a dumb question, but the coffee has not taken hold, yet. Why the left join?
    Cause I'm a SCRUB

    You need an INNER JOIN...

    [homer]
    dooooh
    [/homer]
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    But if I had a nickle for every time someone asked me "Why are you doing it THAT way?".....

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Originally posted by MCrowley
    But if I had a nickle for every time someone asked me "Why are you doing it THAT way?".....
    Ya know...I usually TEST my answer BEFORE I post them.....

    That'll Learn me....(yeah right...)

    One could hope....

    oneleg_theone (where did you come up with that) did that fix it for you?

    Sorry about the earlier...my bad...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Jul 2003
    Posts
    123
    It selects some rows that appears to be duplivates... I haven't had the time to check if it is exactly what I need but it appears to be.. :-)

    I'm not so used to JOINs... not yet but I figure I learn.

    "Oneleg The One" originates from some cartoon creatures I draw once upon a time that had one leg. I usually call myself "Oneleg" or "Oneleg The One" sometimes even "Oneleg The One And Only"... I myself do have two legs..

  11. #11
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    'S Okay. I'm not really blind.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •