2 Replies Latest reply: Sep 26, 2014 10:17 AM by Massimo Grossi RSS

    Extract Duplicate Data to a new Table

      Hi,

      I have received an issue from on of our users who would like to extract duplicate data in a field into another table.  The data is as follows:

      LOAD * INLINE [

          Unique_No, City, Cust_Name,

          1, London, Jeff Daniels,

          2, Birmingham, Colin Powell

          3, London, Frank Harrison

          4, Birmingham, Russell Crowe

          5, Aberdeen, Reggie Yates

          6, London, Donald Donaldson

          7, Slough, Rita Sutherland

          8, Reading, Jamie Curtis

          9, Birmingham, Lois Griffin

          10, Liverpool, Megan Clarke

      ];

       

      Basically what we are looking for, the column city had the duplicate, is there a way we can get the data out so one table had all the data above, but another table only has the duplicate data (Which just happens to be Birmingham and London)

      Table should show as

       

      Unique_no      City                Cust_Name

      1                     London           Jeff Daniles

      2                     Birmingham    Colin Powell

      3                     London           Frank Harrison

      4                     Birmingham    Russell Crowe

      6                     London           Donald Donaldson

      9                     Birmingham    Lois Griffin

       

      Regards,

       

      Jon