SQL query to remove duplicates based on the modified date

Question

I have a database table with the following columns regularization_dt, modified_dt and account_id. For a particular whenever an account is updated an entry is added in to the table with new modified_dt for the regularization_dt and account_id.

I want an SQL query to remove duplicates of regularization. Only one entry for the regularization should be selected based on the recent modified_dt.

For example the entries in the table are as follows:

regularization_dt | account_id | modified_dt
----------------- | ---------- | ----------
03-28-2013        |  123       |05-26-2014
03-28-2013        |  123       |01-14-2014
05-26-2014        |  123       |05-25-2014

The resulting query should yield:

regularization_dt | account_id | modified_dt
----------------- | ---------- | ----------
03-28-2013        |  123       |05-26-2014    
05-26-2014        |  123       |05-25-2014

How should I remove duplicates of a column based on a different column?

select * from history where account_id = 123;

http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows ? — Marc B, Oct 21 '14 at 15:35
Could you reviews your tags. Are your looking for a solution for [tag:oracle] or [tag:mysql] ? Or a cross-vendor solution ? — Sylvain Leroux, Oct 21 '14 at 15:41

score 0 · Answer 1 · answered Oct 21 '14 at 15:44

0

Sounds like you want to use an aggregate function and a GROUP BY :

SELECT   regularization_dt,
         account_id,
         MAX(modified_dt) as modified_dt
FROM     yourtable
GROUP BY regularization_dt,
         account_id;

This should return deduplicated rows with only the latest modified_dt for rows with identical regularization_dt and account_id column values.

answered Oct 21 '14 at 15:44

XVar

436
4
15

What should I do if I have another column domain_item_id which also might be repeated? – Arun Oct 21 '14 at 15:51

SQL query to remove duplicates based on the modified date

1 Answers1