MySQL 8.0

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

CREATE TABLE orders (
  order_number VARCHAR(6) NOT NULL,
  order_year YEAR NOT NULL,
  PRIMARY KEY (order_number, order_year)
);
INSERT INTO orders VALUES
  ('A111', 2023),
  ('A111', 2020),
  ('A222', 2021),
  ('A222', 2022),
  ('A333', 2022);

Records: 5  Duplicates: 0  Warnings: 0


SELECT orders.* 
FROM orders INNER JOIN (
  SELECT order_number, MAX(order_year) order_year 
  FROM orders 
  GROUP BY order_number
) x ON orders.order_number = x.order_number AND orders.order_year = x.order_year


order_number
order_year


A111
2023

A222
2022

A333
2022