MySQL 8.0

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

create table character_names(character_id int, lang varchar(10), name varchar(100));
insert into character_names values
(1, 'ENG', 'DONALD DUCK'),
(1, 'ENG', 'GOOD OL'' DONALD'),
(1, 'SWE', 'KALLE ANKA'),
(1, 'SWE', 'KALLEN'),
(2, 'ENG', 'MICKEY MOUSE'),
(2, 'SWE', 'MUSSE PIGG'),
(2, 'SWE', 'MUSEN'),
(3, 'ENG', 'GOOFY'),
(3, 'NOR', 'FEDTMULE')
WITH cte AS (
    SELECT *, RANK() OVER (PARTITION BY character_id ORDER BY CASE
        WHEN lang = 'NOR' THEN 1
        WHEN lang = 'ENG' THEN 2
        ELSE 3
    END) AS rnk
    FROM character_names
)
SELECT *
FROM cte
WHERE rnk = 1


character_id
lang
name
rnk


1
ENG
DONALD DUCK
1

1
ENG
GOOD OL' DONALD
1

2
ENG
MICKEY MOUSE
1

3
NOR
FEDTMULE
1


SELECT *
FROM character_names AS t1
WHERE lang = 'NOR'
OR lang = 'ENG' AND NOT EXISTS (
    SELECT *
    FROM character_names AS t2
    WHERE t2.character_id = t1.character_id
    AND t2.lang = 'NOR'
)


character_id
lang
name


1
ENG
DONALD DUCK

1
ENG
GOOD OL' DONALD

2
ENG
MICKEY MOUSE

3
NOR
FEDTMULE

character_id	lang	name	rnk
1	ENG	DONALD DUCK	1
1	ENG	GOOD OL' DONALD	1
2	ENG	MICKEY MOUSE	1
3	NOR	FEDTMULE	1