MariaDB 10.6

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE tab1 (
  `Col1` VARCHAR(5),
  `Col2` VARCHAR(2),
  `col3` INTEGER
);

INSERT INTO tab1
  (`Col1`, `Col2`, `col3`)
VALUES
  ('code1', 'US', '9'),
  ('code1', '0', '5'),
  ('code2', 'US', '4'),
  ('code2', '0', '11'),
  ('code3', '0', '11');
SELECT * FROM tab1 WHERE col3 = 
(SELECT MAX(col3) FROM tab1  WHERE (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0')))
AND (col1 IN ('code1', 'code2') ) AND ((col2 = 'US') OR (col2 = '0'))
ORDER BY col1,col2 DESC
LIMIT 1


Col1
Col2
col3


code2
0
11


SELECT
	col1,col2,col3
FROM
	(SELECT
		`Col1`, `Col2`, `col3`,
		ROW_NUMBER() OVER(ORDER BY col2 DESC,col3 DESC) rn2
	FROM(
		SELECT 
			* ,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3 DESC) rn
		FROM tab1) t1
	WHERE rn = 1) t2
WHERE rn2 = 1


col1
col2
col3


code1
US
9


CREATE TABLE tab2 (
  `Col1` VARCHAR(5),
  `Col2` VARCHAR(2),
  `col3` INTEGER
);

INSERT INTO tab2
  (`Col1`, `Col2`, `col3`)
VALUES
  ('code1', '0', '5'),
  ('code2', 'US', '4'),
  ('code2', '0', '11'),
  ('code3', '0', '11');
SELECT
	col1,col2,col3
FROM
	(SELECT
		`Col1`, `Col2`, `col3`,
		ROW_NUMBER() OVER(ORDER BY col2 DESC,col3 DESC) rn2
	FROM(
		SELECT 
			* ,ROW_NUMBER() OVER(PARTITION BY col1 ORDER BY col3 DESC) rn
		FROM tab2) t1
	WHERE rn = 1) t2
WHERE rn2 = 1


col1
col2
col3


code2
0
11