CODEWITHSUNDEEP
mysqlsql
0101
0101

Reputation: 2706

Check number of rows and delete the last one which is over the limit

Currently I am using the following solution to check number of records and then delete the last one of them which is over the limit. Pseudocode:

var count = SELECT COUNT(*) AS count FROM table WHERE user_id = ?;

if (count > LIMIT) { // e.g [LIMIT == 5]
    DELETE FROM table WHERE user_id = ? ORDER BY last_activity LIMIT 1;
}

The problem however is that I am not happy about this solution. I would like to or rather prefer to do all this using one sql query only.

The type of last_activity column is DATETIME.

Upvotes: 0

Views: 99

Answers (2)

fthiella
fthiella

Reputation: 49049

To keep everything in a single query, you could use something like this:

DELETE FROM table
WHERE
  user_id = ?
  AND last_activity = (SELECT * FROM
   (SELECT CASE WHEN COUNT(*)>5 THEN MIN(last_activity) END
    FROM table
    WHERE user_id = ?) s)

subquery will return the first last_activity if there are more than 5 activities, and the DELETE query will delete it. Otherwise it will return NULL and no row will be deleted.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269693

You can use join in a delete statement to delete all but the most recent row, when there are more than five rows for a user:

delete t from table t join
              (select user_id, max(last_activity) as mla
               from table
               group by user_id
               having count(*) > 5
              ) mla
              on t.user_id = mla.user_id and
                 t.last_activity < mla.mla;

That does it for all users. For one user, add a where statement:

delete t from table t join
              (select user_id, max(last_activity) as mla
               from table
               where user_id = '?'
               group by user_id
               having count(*) > 5
              ) mla
              on t.user_id = mla.user_id and
                 t.last_activity < mla.mla;

EDIT:

To delete one row, just use min() instead of max():

delete t from table t join
              (select user_id, min(last_activity) as mla
               from table
               where user_id = '?'
               group by user_id
               having count(*) > 5
              ) mla
              on t.user_id = mla.user_id and
                 t.last_activity = mla.mla;

Upvotes: 1

Related Questions