MySQL get every first 3 rows for every two other columns

Question

What I'm trying to achieve in mysql is the following:

I have a table with the following data:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(a CHAR(1) NOT NULL
,b CHAR(1) NOT NULL
,factor INT NOT NULL
,PRIMARY KEY(a,b,factor)
);

INSERT INTO my_table VALUES
('A','X',90), -- should be included
('A','X',80), -- should be included
('A','X',70), -- should be included
('A','X',60), -- should NOT be included
('A','Y',70), -- should be included
('A','Y',60), -- should be included
('A','Y',50), -- should be included
('A','Y',40); -- should NOT be included

What I would like is my query to return every first three rows per combination of column_aand column_b. So that the highest 3 rows of factor will remain.

+---+---+--------+
| a | b | factor |
+---+---+--------+
| A | X |     90 |
| A | X |     80 |
| A | X |     70 |
| A | Y |     70 |
| A | Y |     60 |
| A | Y |     50 |
+---+---+--------+

I already found this solution but this one works with only one column instead of two.

Any thoughts?

Answer 1

For versions of MySQL pre-8.0...

SELECT x.*
  FROM my_table x 
  JOIN my_table y 
    ON y.a = x.a 
   AND y.b = x.b 
   AND y.factor >= x.factor 
 GROUP 
    BY x.a
     , x.b
     , x.factor 
HAVING COUNT(*) <= 3 
 ORDER 
    BY a
     , b
     , factor DESC;

...and for next time, please see: Why should I provide an MCRE for what seems to me to be a very simple SQL query?