2

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.

2
  • 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)? Apr 8, 2013 at 16:23
  • I'd like to keep the N highest I'd per char,category.
    – janwo
    Apr 9, 2013 at 15:26

4 Answers 4

2

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

2
  • 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?
    – janwo
    Apr 9, 2013 at 19:04
  • @daffunn yes, I have updated the answer (option 2), hope this helps
    – Akash
    Apr 10, 2013 at 3:03
1

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 !!!

8
  • Did my answer solve your problem ??? Apr 9, 2013 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
    – janwo
    Apr 9, 2013 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. Apr 9, 2013 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.
    – janwo
    Apr 9, 2013 at 19:15
  • OMG I actually solved it. Please look it over !!! Apr 9, 2013 at 20:25
0

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.

2
  • 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.
    – janwo
    Apr 8, 2013 at 15:29
  • We want to delete everything except x most recent. This query deletes the x oldest entries. Jun 28, 2019 at 11:39
0

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

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.