Current Data:
| id | name | salary |
|---|---|---|
| 1 | ARU | 100000 |
| 2 | ARU | 100000 |
| 3 | AMY | 200000 |
| 4 | KAT | 400000 |
Expected Output: (After deleting the duplicates)
| id | name | salary |
|---|---|---|
| 1 | ARU | 100000 |
| 3 | AMY | 200000 |
| 4 | KAT | 400000 |
Loading
How to delete duplicate data keeping the latest record in Table? (SQL)
3 Answers
WITH Data AS (
SELECT
Columnsname,
ROW_NUMBER() OVER (PARTITION BY ColumnDuplicated ORDER BY ColumnDate DESC) AS A
FROM
TableName
)
DELETE FROM Data WHERE A > 1;
Shramik AdhikariPosted Jan 16, 2024, 10:23 AM
You may use the below sample query to remove duplicate records.
DELETE FROM your_table
WHERE (unique_column1, unique_column2, created_at) IN
(
SELECT unique_column1, unique_column2, MAX(created_at)
FROM your_table
GROUP BY unique_column1, unique_column2
HAVING COUNT(*) > 1
);
This SQL statement deletes duplicates by grouping the records based on unique_column1, unique_column2 and keeps only the latest record within each group. It uses MAX(created_at) to identify the latest record and removes duplicates where there are multiple records for the same unique columns.
Alpesh ManiyaPosted Jan 5, 2024, 12:56 PM
with cte
AS
(
select *
from
(
select *,row_number() over(partition by name order by id asc)as rowno
from table
)c
where c.rowno>1
)
delete
from cte
Nandan HegdePosted Jan 5, 2024, 11:33 AM