Delete Old Records in SQL While Keeping the Most Recent Entry

When working with time-series or transactional data, it’s common to clean up old records for performance and storage optimization. However, there are situations where you need to delete older records while retaining at least one recent entry per group or limiting the number of rows deleted at a time.

In this article, we will explore SQL strategies for:

• Deleting records older than a given date.
• Retaining the latest record for each group (e.g., per user or account).
• Deleting with a row limit using LIMIT or equivalent approaches.

✅ Use Case Example

Let’s say you have a table called securitiesPosition with the following schema:

CREATE TABLE securitiesPosition (
    id SERIAL PRIMARY KEY,
    securitiesAccountResourceId VARCHAR(100),
    reportDateTime TIMESTAMP,
    other_fields ...
);

Your goals might include:

1. Deleting records older than a certain date.
2. Ensuring that at least one record per securitiesAccountResourceId is kept.
3. Optionally, deleting only a fixed number of rows at a time (e.g., for batch processing).

SQL Pattern 1: Delete Older Records but Keep the Latest per Group

This query removes old records only if they are not the most recent per group.

DELETE FROM securitiesPosition
WHERE id NOT IN (
    SELECT id FROM (
        SELECT sp.id
        FROM securitiesPosition sp
        JOIN (
            SELECT securitiesAccountResourceId, MAX(reportDateTime) AS maxTime
            FROM securitiesPosition
            GROUP BY securitiesAccountResourceId
        ) latest
        ON sp.securitiesAccountResourceId = latest.securitiesAccountResourceId
           AND sp.reportDateTime = latest.maxTime
    ) AS keep_ids
)
AND reportDateTime < '2024-01-01';

Explanation

• The inner-most subquery gets the latest reportDateTime per securitiesAccountResourceId.
• The second level joins to get the corresponding ids.
• The outer DELETE removes everything else that is older than the date and not in the keep list.

SQL Pattern 2: Delete with a Row Limit (MySQL)

In MySQL, you can use LIMIT in a DELETE query:

DELETE FROM securitiesPosition
WHERE securitiesAccountResourceId = 'ABC123'
ORDER BY reportDateTime ASC
LIMIT 100;

Use Case:

This query deletes up to 100 of the oldest records for a given account. It’s useful in cron jobs or background cleanup tasks to avoid locking large tables.

SQL Pattern 3: Limited Delete in PostgreSQL

PostgreSQL doesn’t support LIMIT directly in DELETE, but you can simulate it:

DELETE FROM securitiesPosition
WHERE id IN (
    SELECT id FROM securitiesPosition
    WHERE securitiesAccountResourceId = 'ABC123'
    ORDER BY reportDateTime ASC
    LIMIT 100
);

This performs a safe, paginated delete using a subquery.

🔄 Best Practices

• Always run a SELECT query first to confirm which rows will be deleted.
• Create backups before running destructive operations.
• Use transactions if supported by your DB to allow rollback.
• Consider adding indexes on reportDateTime and securitiesAccountResourceId for performance.

Summary

Deleting old records while retaining critical data such as the most recent entry is a frequent requirement in database maintenance. Whether you’re using MySQL, PostgreSQL, or another SQL dialect, you have tools at your disposal:

By applying the right pattern, you can keep your database lean and fast—without risking the loss of your most important data.