CODEWITHSUNDEEP
sqlms-access
Fabrice76
Fabrice76

Reputation: 63

Access SQL query to delete all but last max date with criteria

I've got a table with a login and a date and i want to keep only the N last connections for each records with a special external id

Ex : I want to keep the last 2 connections (the 2 latest date for each idexternal) in my table, so i'd like the best delete query to do that (is there another way than a "where not in" ?). I've got several million records in my table...

Table : login

id, idExternal, DateLogin
 1,          1, 2019/09/20 -> DELETE 
 2,          1, 2019/09/21 -> DELETE
 3,          1, 2019/09/22 -> KEEP
 4,          1, 2019/09/23 -> KEEP
 5,          2, 2019/09/20 -> DELETE 
 6,          2, 2019/09/21 -> DELETE
 7,          2, 2019/09/22 -> KEEP
 8,          2, 2019/09/24 -> KEEP
 9,          3, 2019/09/23 -> DELETE
10,          3, 2019/09/24 -> KEEP
11,          3, 2019/09/25 -> KEEP
12,          4, 2019/09/22 -> KEEP

How can do that only with a sql query ?

Thanks

Upvotes: 2

Views: 581

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You can use a correlated subquery. Here is one method:

select t.*
from t
where t.datelogin in (select top (2) t2.datelogin
                      from t as t2
                      where t2.idExternal = t.idExternal
                      order by t2.datelogin desc
                     );

For a delete:

delete from t
    where t.datelogin not in (select top (2) t2.datelogin
                              from t as t2
                              where t2.idExternal = t.idExternal
                              order by t2.datelogin desc
                             );

An index on (idExternal, datelogin) will help this query. However, if you have to delete a lot of rows, the query will be inefficient. It is often better to create a new table with only the rows you want, truncate the old table, and reinsert the values.

Upvotes: 3

Related Questions