MySql - Aggregate function for choosing first choice, second choice, third choice, etc?

Question

Imagine I have the following data in a table called "messages":

message_id | language_id | message
------------------------------------
1            en            Hello
1            de            Hallo
1            es            Hola
2            en            Goodbye
2            es            Adios

(Note that I don't have a German translation for "Goodbye.")

I want to select the messages for a user who speaks English and German, but prefers German.

Meaning, I want a result set that looks like:

message_id | language_id | message
------------------------------------
1            de            Hallo
2            en            Goodbye

But, um, it's proving tricky. Any ideas?

Answer 1

select message_id, language_id, message
from
(select if(language_id="de",0,1) as choice, m.*
 from messages m where m.language_id in ("de","en")
 order by choice) z
group by message_id

Set up your preferences via the "if" in the select to force preferred language to the top of the result set, so group by will select it.

You could also do this, but the response above is probably neater for what you want to use it for.

select *
from messages m where m.language_id = "de" or
 (language_id = "en" and not exists (select 1 from messages n
                                  where n.language_id = "de" and
                                    n.message_id = m.message_id))

Further to your comments. If you are uncomfortable with using the particular MySQL behaviour of GROUP BY (without aggregate functions), then you could use this more standard code:

select *
 from messages m where m.language_id in ("de","en")
  and if(m.language_id="de",0,1) <= (select min(if(n.language_id="de",0,1))
 from messages n where n.message_id = m.message_id)

Answer 2

This query will do exactly what you need:

SELECT * FROM (
    SELECT * FROM messages
    WHERE language_id IN ('en', 'de')
    ORDER BY FIELD(language_id, 'en', 'de') DESC
) m
GROUP BY message_id;

Languages in FIELD(language_id, 'en', 'de') should be ordered by priority: the latest one ("de" in this case) will have higher priority, then "en", then all others.

WHERE clause is optional here and is required only if you don't want any results in cases when there is no translation for neither "en" nor "de".

Edit: Sean mentioned the fact that a GROUP BY clause on non-aggregate columns may produce unreliable results. This may be true, at least MySQL Manual says so (though in practice, the first matching row is always (?) used).

Anyway, there is another query with the same idea, but without the mentioned problem.

SELECT m1.*
FROM messages AS m1
INNER JOIN (
    SELECT message_id, MAX(FIELD(language_id, 'en', 'de')) AS weight
    FROM messages
    WHERE language_id IN ('en', 'de')
    GROUP BY message_id
) AS m2
USING(message_id)
WHERE FIELD(m1.language_id, 'en', 'de') = m2.weight;