title: How to remove duplicate records from table using MAX function
body: <p>I have a table t1 that looks similar to the following:</p>
  
  <pre>
  first_name  last_name   row_number
  Bob         Smith       1
  Mike        Jones       2
  Mike        Jones       3
  Jessie      Lee         4
  Bob         Smith       5
  Jessie      Lee         6
  </pre>
  
  <p>and I would like to delete rows from the table so that each name is listed only once and is accompanied by its MAX row number. </p>
  
  <p>As such I would like the output of my query to be:</p>
  
  <pre>
  first_name  last_name   row_number
  Mike        Jones       3
  Bob         Smith       5
  Jessie      Lee         6
  </pre>
  
  <p>The query that I came up with is:</p>
  
  <pre>
  DELETE FROM table t1
  WHERE t1.row_number !=
  (SELECT MAX(row_number) FROM table t2
  WHERE t1.first_name = t2.first_name and t2.last_name = t2.last_name);
  </pre>
  
  <p>This query does not work (it deletes some rows but not the right ones) but I don't understand what I am doing wrong. How can I fix this query to delete the correct rows?</p>