CODEWITHSUNDEEP
sqloracle-database
offcenter35
offcenter35

Reputation: 145

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?

Upvotes: 2

Views: 1717

Answers (1)

Jeremy Gurr
Jeremy Gurr

Reputation: 1623

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

Upvotes: 0

Related Questions