offcenter35 offcenter35 - 6 days ago 5
SQL Question

How to remove duplicate records from table using MAX function

I have a table t1 that looks similar to the following:


first_name last_name row_number
Bob Smith 1
Mike Jones 2
Mike Jones 3
Jessie Lee 4
Bob Smith 5
Jessie Lee 6


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.

As such I would like the output of my query to be:


first_name last_name row_number
Mike Jones 3
Bob Smith 5
Jessie Lee 6


The query that I came up with is:


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);


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?

Answer

You have a typo: "and t2.last_name = t2.last_name" should probably be: "and t1.last_name = t2.last_name"

Comments