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.

I´d like to delete the oldest entries of my database, but in each category shall remain at least N words of each char.

Structure of TABLE words

id | word | char | category

For example I have the following entries:

id | word | char | category
1     abc     a       1
2     abc     a       1
3     abc     a       1
4     bcs     b       1
5     bcs     b       1
6     bcs     b       1
7     csd     c       2
8     csd     c       2
9     asd     a       2
10    asc     a       2

For N=2 the following entries should be removed as their id´s are the lowest:

1     abc     a       1
4     bcs     b       1

Can somebody help? I tried various things (GROUP BY, HAVING, INNER AND OUTER JOINS) but it does not work.

share|improve this question
    
What exactly do you want to to keep? The N "youngest" - highest id - per (char, category) or the N highest id per (word, char, category)? –  ypercube Apr 8 '13 at 16:23
    
I'd like to keep the N highest I'd per char,category. –  daffunn Apr 9 '13 at 15:26

4 Answers 4

OMG this was very hard but here it goes:

This will give you the IDs that need to be deleted

SET @N = 2;
SET @X = 0;
SET @P = 0;
SET @G = 0;
SET @Z = 0;
SELECT id FROM
(SELECT *,@Z:=IF(@P<G,1,@Z+1) Z,@P:=G,
maxcount+1-@z dif,maxcount mx
FROM (SELECT AA.*,BB.G FROM
(SELECT B.*,@X:=@X+1 X,A.maxcount FROM
(SELECT word,category,COUNT(1) maxcount
FROM words GROUP BY word,category
HAVING COUNT(1) > @N) A INNER JOIN words B
USING (word,category)) AA INNER JOIN
(SELECT *,@G:=@G+1 G FROM (SELECT DISTINCT
word,category FROM words) BBB) BB
USING (word,category)) HH
ORDER BY id) AAA
WHERE (mx+1-z) > @N;

Here is the sample data:

mysql> DROP DATABASE IF EXISTS daffunn;
Query OK, 1 row affected (0.01 sec)

mysql> CREATE DATABASE daffunn;
Query OK, 1 row affected (0.00 sec)

mysql> USE daffunn
Database changed
mysql> CREATE TABLE words
    -> (
    ->     id INT NOT NULL AUTO_INCREMENT,
    ->     word VARCHAR(20),
    ->     `char` CHAR(1),
    ->     category INT,
    ->     PRIMARY KEY (id)
    -> );
Query OK, 0 rows affected (0.07 sec)

mysql> INSERT INTO words (word,category) VALUES
    -> ('abc',1),('abc',1),('abc',1),('bcs',1),('bcs',1),
    -> ('bcs',1),('csd',2),('csd',2),('asd',2),('asc',2),
    -> ('asd',2),('asd',2),('asd',2),('asd',2),('asd',2),('asd',2);
Query OK, 16 rows affected (0.00 sec)
Records: 16  Duplicates: 0  Warnings: 0

mysql> UPDATE words SET `char` = LEFT(word,1);
Query OK, 16 rows affected (0.00 sec)
Rows matched: 16  Changed: 16  Warnings: 0

Here is the output:

mysql> SELECT * FROM words;
+----+------+------+----------+
| id | word | char | category |
+----+------+------+----------+
|  1 | abc  | a    |        1 |
|  2 | abc  | a    |        1 |
|  3 | abc  | a    |        1 |
|  4 | bcs  | b    |        1 |
|  5 | bcs  | b    |        1 |
|  6 | bcs  | b    |        1 |
|  7 | csd  | c    |        2 |
|  8 | csd  | c    |        2 |
|  9 | asd  | a    |        2 |
| 10 | asc  | a    |        2 |
| 11 | asd  | a    |        2 |
| 12 | asd  | a    |        2 |
| 13 | asd  | a    |        2 |
| 14 | asd  | a    |        2 |
| 15 | asd  | a    |        2 |
| 16 | asd  | a    |        2 |
+----+------+------+----------+
16 rows in set (0.00 sec)

mysql>

Here is that query to bring back IDs that need to be deleted:

mysql> SET @N = 2;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @X = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @P = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @G = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SET @Z = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT id FROM
    -> (SELECT *,@Z:=IF(@P<G,1,@Z+1) Z,@P:=G,
    -> maxcount+1-@z dif,maxcount mx
    -> FROM (SELECT AA.*,BB.G FROM
    -> (SELECT B.*,@X:=@X+1 X,A.maxcount FROM
    -> (SELECT word,category,COUNT(1) maxcount
    -> FROM words GROUP BY word,category
    -> HAVING COUNT(1) > @N) A INNER JOIN words B
    -> USING (word,category)) AA INNER JOIN
    -> (SELECT *,@G:=@G+1 G FROM (SELECT DISTINCT
    -> word,category FROM words) BBB) BB
    -> USING (word,category)) HH
    -> ORDER BY id) AAA
    -> WHERE (mx+1-z) > @N;
+----+
| id |
+----+
|  1 |
|  4 |
|  9 |
| 11 |
| 12 |
| 13 |
| 14 |
+----+
7 rows in set (0.00 sec)

mysql>

Please don't ask me how I made this. It took a lot of thought and trial-and-error.

Anyway, take this query and create the DELETE for it:

SET @N = 2;
SET @X = 0;
SET @P = 0;
SET @G = 0;
SET @Z = 0;
SELECT CONCAT(GROUP_CONCAT(id),',-1')
INTO @IDLIST FROM
(SELECT *,@Z:=IF(@P<G,1,@Z+1) Z,@P:=G,
maxcount+1-@z dif,maxcount mx
FROM (SELECT AA.*,BB.G FROM
(SELECT B.*,@X:=@X+1 X,A.maxcount FROM
(SELECT word,category,COUNT(1) maxcount
FROM words GROUP BY word,category
HAVING COUNT(1) > @N) A INNER JOIN words B
USING (word,category)) AA INNER JOIN
(SELECT *,@G:=@G+1 G FROM (SELECT DISTINCT
word,category FROM words) BBB) BB
USING (word,category)) HH
ORDER BY id) AAA
WHERE (mx+1-z) > @N;
SET @SQLSTMT:=CONCAT('DELETE FROM words WHERE id IN (',@IDLIST,')');
SELECT @SQLSTMT;
SELECT * FROM words;
PREPARE stmt FROM @SQLSTMT; 
EXECUTE stmt; 
DEALLOCATE PREPARE stmt;
SELECT * FROM words;

Ready? Here it goes:

mysql> SET @N = 2;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @X = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @P = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @G = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SET @Z = 0;
Query OK, 0 rows affected (0.00 sec)
mysql> SELECT CONCAT(GROUP_CONCAT(id),',-1')
    -> INTO @IDLIST FROM
    -> (SELECT *,@Z:=IF(@P<G,1,@Z+1) Z,@P:=G,
    -> maxcount+1-@z dif,maxcount mx
    -> FROM (SELECT AA.*,BB.G FROM
    -> (SELECT B.*,@X:=@X+1 X,A.maxcount FROM
    -> (SELECT word,category,COUNT(1) maxcount
    -> FROM words GROUP BY word,category
    -> HAVING COUNT(1) > @N) A INNER JOIN words B
    -> USING (word,category)) AA INNER JOIN
    -> (SELECT *,@G:=@G+1 G FROM (SELECT DISTINCT
    -> word,category FROM words) BBB) BB
    -> USING (word,category)) HH
    -> ORDER BY id) AAA
    -> WHERE (mx+1-z) > @N;
Query OK, 1 row affected (0.00 sec)

mysql> SET @SQLSTMT:=CONCAT('DELETE FROM words WHERE id IN (',@IDLIST,')');
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT @SQLSTMT;
+------------------------------------------------------+
| @SQLSTMT                                             |
+------------------------------------------------------+
| DELETE FROM words WHERE id IN (1,4,9,11,12,13,14,-1) |
+------------------------------------------------------+
1 row in set (0.00 sec)

mysql>

Here is the DELETE (Before and After)

mysql> SELECT * FROM words;
+----+------+------+----------+
| id | word | char | category |
+----+------+------+----------+
|  1 | abc  | a    |        1 |
|  2 | abc  | a    |        1 |
|  3 | abc  | a    |        1 |
|  4 | bcs  | b    |        1 |
|  5 | bcs  | b    |        1 |
|  6 | bcs  | b    |        1 |
|  7 | csd  | c    |        2 |
|  8 | csd  | c    |        2 |
|  9 | asd  | a    |        2 |
| 10 | asc  | a    |        2 |
| 11 | asd  | a    |        2 |
| 12 | asd  | a    |        2 |
| 13 | asd  | a    |        2 |
| 14 | asd  | a    |        2 |
| 15 | asd  | a    |        2 |
| 16 | asd  | a    |        2 |
+----+------+------+----------+
16 rows in set (0.00 sec)

mysql> PREPARE stmt FROM @SQLSTMT;
Query OK, 0 rows affected (0.04 sec)
Statement prepared

mysql> EXECUTE stmt;
Query OK, 7 rows affected (0.02 sec)

mysql> DEALLOCATE PREPARE stmt;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM words;
+----+------+------+----------+
| id | word | char | category |
+----+------+------+----------+
|  2 | abc  | a    |        1 |
|  3 | abc  | a    |        1 |
|  5 | bcs  | b    |        1 |
|  6 | bcs  | b    |        1 |
|  7 | csd  | c    |        2 |
|  8 | csd  | c    |        2 |
| 10 | asc  | a    |        2 |
| 15 | asd  | a    |        2 |
| 16 | asd  | a    |        2 |
+----+------+------+----------+
9 rows in set (0.00 sec)

mysql>

I also tried this in your SQL Fiddle. IT WORKS !!!

PLEASE GIVE IT A TRY !!!

share|improve this answer
    
Did my answer solve your problem ??? –  RolandoMySQLDBA Apr 9 '13 at 17:36
    
Hi Rolando, sorry for my late response and thank you for your answer. I´d like to have the youngest id per char and category, but your suggestion seems to distinguish between word and category. I rewrote your code but it is still not working. Normally all entries (id > 13) should be selected. Look at this sqlfiddle.com/#!2/7dcb97/4 –  daffunn Apr 9 '13 at 18:56
    
I am confused now. I thought the question was to delete everything but the last two rows per word and category. As my DELETE query shows, the two rows you specified should be deleted were deleted as the question suggested. –  RolandoMySQLDBA Apr 9 '13 at 18:59
    
You were nearly right. I wanted to delete everything but the last two rows per char and category. As a result I changed word to char. In the small example everything worked fine but as soon as I add more entries the query failed. In my first comment I said all entries (id>13) should be selected but this was my mistake - I answered to fast without think enough before. So the following entries should be selected: 1, 4, 9, 10, 11, 12, 13, 14. –  daffunn Apr 9 '13 at 19:15
    
OMG I actually solved it. Please look it over !!! –  RolandoMySQLDBA Apr 9 '13 at 20:25

If I understand you requirements clearly, you need to delete rows that are grouped on the basis of char and category (leaving minimum latest N rows)

Updated: Temporary table workaround

This can be achieved by

Option 1:

create temporary table test_table_rows_temp SELECT * from test;

DELETE t1
FROM 
    test  as t1
WHERE
    t1.id < ( 
            SELECT 
                min( t2.id ) 
            FROM 
                test_table_rows_temp as t2 
            WHERE t2.category =  t1.category 
                    AND t2.`char` =  t1.`char` 
                    AND t2.id >  t1.ID 
            HAVING count( t2.id ) >= 2 
        ) 

I have created a temporary table to store the results of the test table, this helps to ease out the query

Option 2:

There's a workaround to avoid manually creating a temporary table by using a nested sub-query instead

DELETE t1
FROM 
    test  as t1
WHERE
    t1.id < ( 
            SELECT 
                min( id ) 
            FROM (
                SELECT 
                    id, 
                    category, 
                    `char`
                FROM test
            ) AS t2 
            WHERE t2.category =  t1.category 
                    AND t2.`char` =  t1.`char` 
                    AND t2.id >  t1.ID 
            HAVING count( t2.id ) >= 2 
        );

The nested sub-query internally too creates a temporary table :)

The 2 rows as mentioned above (ids 1, 4) are deleted

The query simply gets minimum - N + 1 id in the group of category, word (ordered by the latest id) and then checks if the current row id is less than the one retrieved (within the same group(s))

SQLFiddle

Hope this helps

share|improve this answer
    
Thank you! This works great. But I had to change word -> char. Look at this sqlfiddle.com/#!2/9ac63/1 Is it possible to make it work without temporary tables? –  daffunn Apr 9 '13 at 19:04
    
@daffunn yes, I have updated the answer (option 2), hope this helps –  Akash Apr 10 '13 at 3:03

I'm not sure if i understand correctly, but for your example this query will delete those two entries:

DELETE FROM words WHERE id IN (SELECT * FROM (SELECT id FROM words GROUP BY `char` HAVING COUNT(`char`) > 2) AS w);

Additional 'SELECT ... AS w' is needed to bypass mysql restriction for those kind of queries.

share|improve this answer
    
Thank you for your answer, but this seems not to work. I have tested it, but it deletes only one entry of every char in a category. –  daffunn Apr 8 '13 at 15:29

Another way, without variables:

DELETE
    del
FROM
    words AS del
  JOIN
    ( SELECT w.`char`, w.category, w.id
      FROM
          ( SELECT DISTINCT `char`, category
            FROM words 
          ) AS d
        JOIN
          words AS w
            ON  w.`char` = d.`char` 
            AND w.category = d.category
            AND w.id =
                ( SELECT i.id
                  FROM words AS i
                  WHERE i.`char` = d.`char`
                    AND i.category = d.category
                  ORDER BY i.id DESC
                    LIMIT 1 
                    OFFSET  @N                    -- to keep N rows per char and category
                ) 
    ) AS tmp
      ON  del.`char` = tmp.`char`
      AND del.category = tmp.category 
      AND del.id <= tmp.id ;

Tested at SQL-Fiddle


And another, using the same concept but slightly simplified:

DELETE
    del
FROM
    words AS del
  JOIN
    ( SELECT `char`, category,
             ( SELECT i.id
               FROM words AS i
               WHERE i.`char` = d.`char`
                 AND i.category = d.category
               ORDER BY i.id DESC
                 LIMIT 1 
                 OFFSET  @N
             ) AS id
      FROM words AS d
      GROUP BY `char`, category
    ) AS tmp
      ON  del.`char` = tmp.`char`
      AND del.category = tmp.category 
      AND del.id <= tmp.id ;

Tested at SQL-Fiddle-2

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.