My table looks like this:

|     id    | <- primary key
|    time   | <- DateTime
|    row2   | <- varchar
|    row3   |
|    ...    |

I want to remove all entries, except one, which are within a timeframe of one full hour and have the same value of row2 (distinct row2). Is it possible to do this without a stored procedure and/or non-sql code?

  • Do you have a primary key on your table? Is it possible that you have more than one row with the value 'row2' in the given period? Nov 3, 2012 at 16:27
  • Yeah I have an id, which ist the primary key. Also it is possible that there exist entries with the same value of row2.
    – icefex
    Nov 3, 2012 at 16:50
  • In this case, do you have a preference among the multiple entries (say the smallest difference from the full hour)? Nov 3, 2012 at 16:52
  • No, as the only difference between them is the timestamp and the precision needs to be only up to full hours.
    – icefex
    Nov 3, 2012 at 16:59
  • Compiled an answer. If I misunderstood your timeframe specs, please tell it immediately :) Nov 3, 2012 at 17:12

2 Answers 2


The usual solution for this problem is first looking for the rows you want to keep, and then delete all others. The first part can be accomplished with an aggregate and grouping:

SELECT min(id)
FROM my_table
GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

Then you can write your DELETE:

DELETE FROM my_table
    SELECT min(id)
    FROM my_table
    GROUP BY row2, DATE_FORMAT(`time`, '%Y-%m-%d %H')

There are variations to this theme: you can use a NOT EXISTS construct instead of NOT IN (with appropriate changes), or a join using the USING keyword.

  • This is almost what i need, I should have been more specific about the timeframe :) Now, what I want to do is doing this for every hour within a day or a longer timeframe.
    – icefex
    Nov 3, 2012 at 17:27
  • That was my other idea :) Editing my answer accordingly. Nov 3, 2012 at 17:31

Back on Feb 22, 2011, I answered a question about a DELETE query using a WHERE clause against itself : ( Problem with MySQL subquery ).

Here is a safer approach:

  • Create a temp table called mytable_key
  • Collect keys with minimum ids per row2 per hour into mytable_key
  • Create a temp table called mytable_new
  • Join all keys in mytable_key with mytable and load it into mytable_new
  • Rename mytable to mytable_old
  • Rename mytable_new to mytable
  • Drop mytable_old
  • Drop mytable_key

Here is the actual SQL:

CREATE TABLE mytable_key
    SELECT id FROM mytable
    WHERE 1=2
INSERT INTO mytable_key
    SELECT MIN(id) FROM mytable
    GROUP BY row2,DATE_FORMAT(`time`,'%Y%m%d%H')
CREATE TABLE mytable_new LIKE mytable;
INSERT INTO mytable_new
    SELECT B.* FROM mytable_key A
    INNER JOIN mytable B USING (id)
ALTER TABLE mytable RENAME mytable_old;
ALTER TABLE mytable_new RENAME mytable;
DROP TABLE mytable_old;
DROP TABLE mytable_key;

You can run these commands as is or place them in a stored procedure.

While I give dezso's answer a +1 because it works mechanically in Oracle, PostgreSQL and SQL Server, take it from a MySQL DBA, I don't trust MySQL's approach to internal SQL optimization via transformation when doing a DELETE on a table against itself. This is why I submitted this answer.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.