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?

How does the rows are ordered? How to determine that the row is 1st, 2nd,.... ? remember - posessional order is not safe. — Akina, Oct 02 '20 at 10:22
Hi, first ordering = `column_a ASC, column_b ASC, factor DESC` — dexter, Oct 02 '20 at 10:23

score 1 · Answer 1 · answered Oct 02 '20 at 10:25

WITH cte AS ( SELECT *, 
                     ROW_NUMBER() OVER ( PARTITION BY column_a, column_b 
                                         ORDER BY  factor DESC ) rn
              FROM table )
SELECT column_a, column_b, factor
FROM cte 
WHERE rn <= 3;

score 1 · Accepted Answer · answered Oct 02 '20 at 10:34

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?

MySQL get every first 3 rows for every two other columns

2 Answers2