SQLite 3.27

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

CREATE TABLE cards (`Name` VARCHAR(19), `lang` VARCHAR(3), `set` VARCHAR(4), `collector_number` VARCHAR(3));
✓
INSERT INTO cards (`Name`, `lang`, `set`, `collector_number`) VALUES
  ('Torrential Gearhulk', 'ru', 'kld', '67'),
  ('Torrential Gearhulk', 'ko', 'kld', '67'),
  ('Torrential Gearhulk', 'zht', 'kld', '67'),
  ('Torrential Gearhulk', 'pt', 'kld', '67'),
  ('Torrential Gearhulk', 'de', 'kld', '67'),
  ('Torrential Gearhulk', 'es', 'kld', '67'),
  ('Torrential Gearhulk', 'zhs', 'kld', '67'),
  ('Torrential Gearhulk', 'en', 'mps', '2'),
  ('Torrential Gearhulk', 'ja', 'kld', '67'),
  ('Torrential Gearhulk', 'fr', 'kld', '67'),
  ('Torrential Gearhulk', 'it', 'kld', '67'),
  ('Torrential Gearhulk', 'en', 'kld', '67'),
  ('Torrential Gearhulk', 'en', 'pkld', '67s');
✓
SELECT Name, lang, `set`, collector_number
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY `set`, collector_number ORDER BY lang = 'ja' DESC) rn 
  FROM cards
  WHERE name = 'Torrential Gearhulk'
)
WHERE rn = 1


Name
lang
set
collector_number


Torrential Gearhulk
ja
kld
67

Torrential Gearhulk
en
mps
2

Torrential Gearhulk
en
pkld
67s


SELECT Name, lang, `set`, collector_number
FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY `set`, collector_number ORDER BY lang = 'en' DESC) rn 
  FROM cards
  WHERE name = 'Torrential Gearhulk'
)
WHERE rn = 1


Name
lang
set
collector_number


Torrential Gearhulk
en
kld
67

Torrential Gearhulk
en
mps
2

Torrential Gearhulk
en
pkld
67s

Name	lang	set	collector_number
Torrential Gearhulk	ja	kld	67
Torrential Gearhulk	en	mps	2
Torrential Gearhulk	en	pkld	67s