CODEWITHSUNDEEP
sqlms-access
thyhmoo
thyhmoo

Reputation: 323

MS-Access: Delete duplicate rows but keep the one with the latest date

I am new to SQL and MS-Access and could need some help. My table looks like this and I want to delete all rows, where C1, C2 and C3 are the same but keep the row with the latest date. In this case I would like to delete the rows with ID 2 and 5.

ID  |  C1  |  C2  |  C3  |  Date   |
1   |  a   |  a   |  b   | 01.04.20|
2   |  a   |  a   |  b   | 01.03.19|
3   |  a   |  b   |  b   | 01.04.20|
4   |  a   |  b   |  c   | 01.04.20|
5   |  a   |  b   |  c   | 07.05.19|

I tried to numerate the rows using ROW_NUMBER OVER (PARTITION BY C1,C2,C3 ORDER BY Date ASC) so I could delete all rows with a Rownumber >1. But unfortunately PARTITION BY doesn't work in Access.

Do you have another idea how I could manage that?

The following query gives me the Table I want. But how can I go on with this?

SELECT C1,C2,C3, max(Date)
FROM Table
GROUP BY C1,C2,C3

Thank you for your help!

Upvotes: 2

Views: 1321

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271151

In MS Access, you can use:

select t.*
from t
where t.date = (select max(t2.date)
                from t as t2
                where t2.c1 = t.c1 and t2.c2 = t.c2 and t2.c3 = t.c3
               );

You can delete the rows with similar logic:

delete from t
where t.date < (select max(t2.date)
                from t as t2
                where t2.c1 = t.c1 and t2.c2 = t.c2 and t2.c3 = t.c3
               );

Upvotes: 1

Related Questions