MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE example (
  name VARCHAR(10),
  year INT,
  month INT
);

INSERT INTO example VALUES
  ('xxxx', 2019, 8),
  ('xxxx', 2019, 7),
  ('xxxx', 2019, 6),
  ('xxxx', 2019, 5),
  ('zzzz', 2019, 5),
  ('zzzz', 2019, 4),
  ('zzzz', 2019, 3),
  ('zzzz', 2019, 2),
  ('zzzz', 2019, 1);

Records: 9  Duplicates: 0  Warnings: 0


SELECT name, year, month FROM (
    SELECT *, IF(@prev <> name, @rn:=0,@rn), @prev:=name, @rn:=@rn+1 AS rn
    FROM example, (SELECT @rn:=0)rn, (SELECT @prev:='')prev
    ORDER BY name ASC, year DESC, month DESC
)t WHERE rn <= 2;


name
year
month


xxxx
2019
8

xxxx
2019
7

zzzz
2019
5

zzzz
2019
4


SELECT name, year, month FROM (
  SELECT name, year, month, ROW_NUMBER() OVER (PARTITION BY name ORDER BY year DESC, month DESC) AS rn
  FROM example
)t WHERE rn <= 2;


name
year
month


xxxx
2019
8

xxxx
2019
7

zzzz
2019
5

zzzz
2019
4