title: Removing redundant data from mysql
body: <p>I have a table in sql which looks like: </p>
  
  <pre><code>        1   1w10    U   UROK_HUMAN  IIGGEFTTIENQPWFAAIYRRHRGGSVTYVCGGSLISPCWVISATHCFID...   
          2   1w11    U   UROK_HUMAN  IIGGEFTTIENQPWFAAIYRRHRGGSVTYVCGGSLISPCWVISATHCFID...   
          3   1w12    U   UROK_HUMAN  IIGGEFTTIENQPWFAAIYRRHRGGSVTYVCGGSLISPCWVISATHCFID...   
          4   1w13    U   UROK_HUMAN  IIGGEFTTIENQPWFAAIYRRHRGGSVTYVCGGSLISPCWVISATHCFID...   
          5   1w14    U   UROK_HUMAN  IIGGEFTTIENQPWFAAIYRRHRGGSVTYVCGGSLISPCWVISATHCFID...
          6   1w15    A   SYT4_RAT    GSPGISGGGGGIPSGRGELLVSLCYQSTTNTLTVVVLKARHLPKSDVSGL...   
          7   1w16    A   SYT4_RAT    GSPGISGGGGGIPSGRGELLVSLCYQSTTNTLTVVVLKARHLPKSDVSGL...   
          8   1w17    B   PDAA_BACSU  MKWMCSICCAAVLLAGGAAQAEAVPNEPINWGFKRSVNHQPPDAGKQLNS...   
          10  1w18    B   SACB_ACEDI  AGVPGFPLPSIHTQQAYDPQSDFTARWTRADALQIKAHSDATVAAGQNSL...   
          12  1w18    E   SACB_ACEDI  MKGGAGVPDLPSLDASGVRLAIVASSWHGKICDALLDGARKVAAGCGLDD...
  </code></pre>
  
  <p>I want to remove duplicate entries but leaving one of them. For instance, I want to keep the first row but remove 2,3,4,5. In short, I want to remove rows which have same column 4 value (here, UROK-HUMAN) but have different col2 and col3 values( here 1w10, 1w11 etc (col2) and U,A,B(col3)). </p>
  
  <p>However, I do not want to remove entry have same col2 and col3 (1w18 B-E) which have same col4 value(SACB-ACEDI).</p>
  
  <p>How can I write and sql statement to delete those rows? I tried to write like and did not work:</p>
  
  <pre><code>SELECT pdb, chain, unp, sekans, COUNT(*) AS ct
  FROM protein
  JOIN (SELECT DISTINCT(unp)
            FROM protein 
            GROUP by pdb) protein2 ON protein2.unp = protein.unp;
  </code></pre>
  
  <p>Thank you very much for your help.</p>