Sqlite GROUP BY priority

Question

+----+------------+------+
| id |   title    | lang |
+----+------------+------+
|  1 | title 1 EN | en   |
|  1 | title 1 FR | fr   |
|  1 | title 1 ZH | zh   |
|  2 | title 2 EN | en   |
|  3 | title 3 ZH | zh   |
+----+------------+------+

this is my table and I want to group by id but I sometimes I need language "en" to have priority and sometimes I need to have language "zh" as priority

SELECT * FROM table GROUP BY id

gives me a list of all uniqe ids but places zh in favor for id 1, is it possible that I can add a priority for language?

My desired output for "en":

+----+------------+------+
| id |   title    | lang |
+----+------------+------+
|  1 | title 1 EN | en   |
|  2 | title 2 EN | en   |
|  3 | title 3 ZH | zh   |
+----+------------+------+

My desired output for "fr":

+----+------------+------+
| id |   title    | lang |
+----+------------+------+
|  1 | title 1 FR | fr   |
|  2 | title 2 EN | en   |
|  3 | title 3 ZH | zh   |
+----+------------+------+

my desired .. on and on :)

but it is possible that i have an extra id with lang "fr" how can i put the fr's in favor then? — Andy Jacobs, Sep 22 '13 at 12:51
`SELECT * FROM table WHERE lang = x;` then I think is all you need... because the GROUP BY will be conflating records anyway right? — d'alar'cop, Sep 22 '13 at 12:57
if i would put a where lang="fr" i wont get records with id 2 and 3 — Andy Jacobs, Sep 22 '13 at 13:00
maybe then the question needs to be more precise. Am I understanding correctly? "I want to get distinct IDs with a specifiable language taking precedence in the title/lang fields, and when it doesn't exist, then random." — d'alar'cop, Sep 22 '13 at 13:20

score 3 · Accepted Answer · answered Sep 22 '13 at 14:30

3

Since SQLite 3.7.11, you can select which record in a group is returned by using MAX or MIN. A boolean expression like lang = 'en' returns either 0 or 1:

SELECT *,
       MAX(lang = 'en')
FROM MyTable
GROUP BY id

If you want to have priorities for more than one language, you can use a CASE expression:

SELECT *,
       MAX(CASE lang
           WHEN 'en' THEN 2
           WHEN 'fr' THEN 1
                     ELSE 0
           END)
FROM MyTable
GROUP BY id

answered Sep 22 '13 at 14:30

CL.

173,858
17
217
259

1

This is a fascinating and scary feature at the same time (in other RDBMs I'm familiar with, including columns that are not aggregates or members of the GROUP BY clause will result in errors or undefined behavior). I see that this feature is mentioned in the release notes for 3.7.11, but I can't find it in the regular documentation. Is the sqlite documentation kept up to date, or am I expected to read all the release notes to learn of its special features? – Steve Broberg Nov 03 '13 at 23:45
The release notes *are* part of the documentation. I'd guess this features was added only because a paying customer wanted it. – CL. Nov 04 '13 at 07:21

Sqlite GROUP BY priority

1 Answers1

Linked