Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
    
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? –  dezso Nov 3 '12 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 '12 at 16:50
    
In this case, do you have a preference among the multiple entries (say the smallest difference from the full hour)? –  dezso Nov 3 '12 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 '12 at 16:59
    
Compiled an answer. If I misunderstood your timeframe specs, please tell it immediately :) –  dezso Nov 3 '12 at 17:12

2 Answers 2

up vote 3 down vote accepted

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
WHERE id NOT IN (
    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.

share|improve this answer
    
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 '12 at 17:27
    
That was my other idea :) Editing my answer accordingly. –  dezso Nov 3 '12 at 17:31
    
Thank you very much :D –  icefex Nov 3 '12 at 17:40

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')
;
ALTER TABLE mytable_key ADD PRIMARY KEY (id);
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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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