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),
  ('xxxx', 2020, 8),
  ('xxxx', 2020, 7),
  ('xxxx', 2020, 6),
  ('xxxx', 2020, 5),
  ('zzzz', 2020, 5),
  ('zzzz', 2020, 4),
  ('zzzz', 2020, 3),
  ('zzzz', 2020, 2),
  ('zzzz', 2020, 1);

Records: 18  Duplicates: 0  Warnings: 0


-- using one column on the partition (name)
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
2020
8

xxxx
2020
7

zzzz
2020
5

zzzz
2020
4


-- using one column on the partition (name)
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
2020
8

xxxx
2020
7

zzzz
2020
5

zzzz
2020
4


-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
    SELECT *, IF(@prev <> name + year, @rn:=0,@rn), @prev:=name + year, @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
2020
8

xxxx
2020
7

xxxx
2019
8

xxxx
2019
7

zzzz
2020
5

zzzz
2020
4

zzzz
2019
5

zzzz
2019
4


-- using two columns on the partition (name and year)
SELECT name, year, month FROM (
  SELECT name, year, month, ROW_NUMBER() OVER (PARTITION BY name, year ORDER BY year DESC, month DESC) AS rn
  FROM example
)t WHERE rn <= 2;


name
year
month


xxxx
2019
8

xxxx
2019
7

xxxx
2020
8

xxxx
2020
7

zzzz
2019
5

zzzz
2019
4

zzzz
2020
5

zzzz
2020
4
name	year	month
xxxx	2020	8
xxxx	2020	7
xxxx	2019	8
xxxx	2019	7
zzzz	2020	5
zzzz	2020	4
zzzz	2019	5
zzzz	2019	4