SQLite GROUP BY preferring a certain value

Question

I've been working on an Android application for a few years in my spare time, a companion app for Magic: the Gathering. I've been trying to figure out a way to include all of the different languages available but would like to only display unique items to my users, based on their language preferences.

As an example, there are 13 different printings of the card 'Torrential Gearhulk' across all languages and expansions. What I would like to do is display the 3 unique printings that are of the user's preferred language or whatever language is available if a record is not found for a specific set for the user's preferred language.

I've included a couple of example results below (irrelevant columns omitted)

SELECT *
FROM `cards`
WHERE `name` = 'Torrential Gearhulk'

+---------------------+------+------+------------------+
| Name                | lang | set  | collector_number |
+---------------------+------+------+------------------+
| 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 *
FROM `cards`
WHERE `name` = 'Torrential Gearhulk'
GROUP BY `set`, `collector_number`

+---------------------+------+------+------------------+
| Name                | lang | set  | collector_number |
+---------------------+------+------+------------------+
| Torrential Gearhulk | ru   | kld  |               67 |
| Torrential Gearhulk | en   | mps  |                2 |
| Torrential Gearhulk | en   | pkld |              67s |
+---------------------+------+------+------------------+

Note that there are only two records with a set of 'pkld' and 'mps' which have 'en' as their language.

I would like a query that would provide the following if I were to run a search for cards named 'Torrential Gearhulk' with a language preference of Japanese (ja).

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

I would like to return the following if the user's preferred language is English (en) or if a record for their preferred language could not be found.

+---------------------+------+------+------------------+
| Name                | lang | set  | collector_number |
+---------------------+------+------+------------------+
| Torrential Gearhulk | en   | kld  |               67 |
| Torrential Gearhulk | en   | mps  |                2 |
| Torrential Gearhulk | en   | pkld |              67s |
+---------------------+------+------+------------------+

I've tried several methods from doing some Google searches but none have given me the results that I am looking for. I honestly do not remember all of the different queries that I have tried but the most recent suggestion can be found here, which resulted in:

SELECT *,
       MAX(`lang` = 'ja')
FROM `cards`
WHERE `name` = 'Torrential Gearhulk'
GROUP BY `set`, `collector_number`

+---------------------+------+------+------------------+--------------------+
| Name                | lang | set  | collector_number | MAX(`lang` = 'ja') |
+---------------------+------+------+------------------+--------------------+
| Torrential Gearhulk | ru   | kld  |               67 |                  1 |
| Torrential Gearhulk | en   | mps  |                2 |                  0 |
| Torrential Gearhulk | en   | pkld |              67s |                  0 |
+---------------------+------+------+------------------+--------------------+

Answer 1

You can do it with ROW_NUMBER() window function:

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

See the demo.
Results:

> Name                | lang | set  | collector_number
> :------------------ | :--- | :--- | :---------------
> Torrential Gearhulk | ja   | kld  | 67              
> Torrential Gearhulk | en   | mps  | 2               
> Torrential Gearhulk | en   | pkld | 67s