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 |
+---------------------+------+------+------------------+--------------------+

I don't know what you want here, partly due to that your queries are _invalid_, since you are doing `SELECT *` with `GROUP BY`. — Tim Biegeleisen, Oct 06 '20 at 04:27
@TimBiegeleisen could you elaborate on what you mean by _invalid_? All of the above queries return the exact results listed below them either from Android or my server. Thanks for taking a look. — Andrew Speers, Oct 06 '20 at 21:32
Invalid means that the query: `SELECT * FROM cards WHERE name = 'Torrential Gearhulk GROUP BY set, collector_number` would not even run in most databases. SQLite allows it but it returns arbitrary rows (as documented), although it seems that it returns the 1st row of each group. — forpas, Oct 07 '20 at 06:51

score 0 · Answer 1 · answered Oct 06 '20 at 04:49

0

Just use nested select and put whichever language you want instead of 'ja':

SELECT *
FROM (select * from cards where `lang` in ('ja', 'en') and `name` = 'Torrential Gearhulk')
GROUP BY `set`, `collector_number`

answered Oct 06 '20 at 04:49

Autocrab

3,474
1
15
15

This would certainly be my go to if all items in my database had an English (en) printing but sadly they do not. Some were only printed in Japanese (ja), Arabic (ar), German (de), French (fr), etc. Unfortunately, the more language codes that I include within the IN operator, the more likely I am to get something that could be displayed in the user's preferred language but isn't. Thanks for taking a look. – Andrew Speers Oct 06 '20 at 21:41

score 0 · Answer 2 · answered Oct 06 '20 at 04:51

0

You can use this :

SELECT * FROM cards WHERE Name='Torrential Gearhulk' ORDER BY CASE WHEN  lang = 'ja' THEN 2 ELSE 1 END desc

answered Oct 06 '20 at 04:51

aryanknp

1,135
2
8
21

This was one of the first places that I started in order to try to get a base for this. Unfortunately, this returns all 13 items and not the 3 unique to `set` and `collector_number`. I did also try wrapping this as a nested select statement but grouping ignores the ordering of the nested statement: `SELECT * FROM (SELECT * FROM cards WHERE name = 'Torrential Gearhulk' ORDER BY CASE WHEN lang = 'ja' THEN 2 ELSE 1 END DESC) AS t GROUP BY set, collector_number` – Andrew Speers Oct 06 '20 at 21:36
@AndrewSpeers if you want particular `set` and `collector_number` , use where clause not `groupby` – aryanknp Oct 07 '20 at 02:49
For this particular use case I do not want a particular `set` or `collector_number` I want to group by `set` and `collector_number`. When using your suggested query all 13 rows are returned when I am trying to get the 3 that have unique `set` and `collector_number` while also prioritizing a specific language. – Andrew Speers Oct 07 '20 at 03:05

score 0 · Accepted Answer · answered Oct 07 '20 at 07:00

0

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

answered Oct 07 '20 at 07:00

forpas

160,666
10
38
76

Thank you for this! I've looked over your suggestion, the demo and have had to do some testing of my own but I believe that you have provided the most correct and elegant solution to this. I've found that my database server is running MySQL 5.7, which doesn't provide the ROW_NUMBER() function but everything else seems to work flawlessly. I'll reach out to my admins regarding upgrading to MySQL 8.0 since I've confirmed that your suggested query works flawlessly there, too. Now to prepare for the post-upgrade fun! – Andrew Speers Oct 07 '20 at 20:16
In the off chance that we're not able to upgrade to MySQL 8.0 for any reason, could you take a look over the queries below and see if you can spot why the first query always returns all 13 rows? I've run a little short on time today but was able to get this far for MySQL 5.7 and curious if another set of eyes might spot what I'm doing wrong here. https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=cf6f8dd735a58c104d9685adba1f5e4d – Andrew Speers Oct 07 '20 at 20:20
For MySql 5.7 check this solution: https://dbfiddle.uk/?rdbms=mysql_5.7&fiddle=6f715ad2ebeb3c582732dad796e06c85 – forpas Oct 08 '20 at 06:41
1

Works perfectly (minus a comma lol)! Thanks a ton for your suggestions and help with this. The server upgrade went through last night without a hitch so I'll be using your initial recommendation but I'll keep this one in mind if I need to work with older MySQL servers again. – Andrew Speers Oct 08 '20 at 19:56

SQLite GROUP BY preferring a certain value

3 Answers3