Nikhil Srivastava Nikhil Srivastava - 5 months ago 21
SQL Question

how to delete and keep latest entry

I am working on a problem where i have table A with columns :

1) Contact number
2) name
3) address
4) date updated


now because there was no primary key defined multiple times record are inserted with same mobile number. I just want to keep the record with latest date update.

Ex:

8018725398 Nikhil Bangalore 22/4/2016
8018725398 Nikhil Chennai 22/05/2016
8018725398 Nikhil lucknow 22/06/2016


Now i want to keep only last record, not the first two or first n records. There are like thousand of record and 100 of them are repeating. How to do this ?

Answer

Depends on your RDBMS, for ANSI-SQL , you can use ROW_NUMBER() :

WITH dups AS (
SELECT ROW_NUMBER() OVER (PARTITION BY contact_Number ORDER BY date_updated DESC) AS duplicate
FROM TableA)

DELETE FROM dups WHERE duplicate>1;

Or with EXISTS() which should work for all RDBMS :

DELETE FROM TableA t
WHERE EXISTS(SELECT 1 FROM TableA s
             WHERE t.contact_number = s.contact_number
               AND t.date_updated < s.date_updated)
Comments