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 !!!
id
- per(char, category)
or the N highestid
per(word, char, category)
?